PQ rocks!! Only if I'm allowed, a formula alternative solution, no PQ, just to have an idea ( if sales data B1:G15 and tax table I2:K2) - main calculation =BYROW(D2:F11*I2:K2*G2:G11,LAMBDA(x,SUM(x))) - single cell formula including all cosmetics =LET(t,B1:G11,x,I2:K2, w,SEQUENCE(ROWS(t)), b,IFERROR(BYROW(INDEX(t,w,{3,4,5})*x*INDEX(t,w,6),LAMBDA(x,SUM(x))),"Total Tax"), HSTACK(INDEX(t,w,{1,2,6}),b) ) Needless to say, sales data can be regular array or table, if table, no refresh ever needed when table receives more data. ✌
Hi Chandeep, Always grateful for your publications, let me ask: Why in the description of your videos, if the topic has been covered in your blog (as in this case) do you not also add the link to the blog page? Your blogs are very rich in information and failure to include the link could prevent your followers from gaining full knowledge of the topics you share on youtube than on your blog. Sincerely
Hello chandeep sir, great video, I was wondering if we can use "Dynamic M Parameters" to pull the Data within Power BI using SQL Store Procedure, I want to pass SP parameters via Dynamic M query parameters,please assist me any blog would also be fine. Thanks🙏
Hi is there a way to separate value of one row into two separate row values where one row is 25% of the value and one cost category while second cost category is 75% of original value...i have solved the problem with 2 separate queryes and appended them with original query where i have filterted the rows so not to duple them..but i assume there is a better solution hehe
Loved this solution, one question foy anyone, if you change the zero's to null, the method fails and any row containing a null will return null, can anyone explain or direct me some information as to why this is.
Why calculate the zeros at all? If I was doing this math manually I would ignore them and use a formula to do all calcs at once left to right on the calculator... I would think fewer calcs is more efficient but I am new at all this so probably missing something. Thus the question! Thanks.
Just to test out the lambda formula =LAMBDA(taxc,taxc2,taxc3,val,(TEXT(((taxc*Tax[@Tax]*val)+(taxc2*Tax[@Tax2]*val)+(taxc3*Tax[@Tax3]*val)),"0.0"))) Then enter the binary indicator tax columns & values column in the formula
Just watched this again and understand it better, but one thing strikes me (aside from your code missing!). None of the solutions is right! None of them rounded down the Total Tax column to one decimal point as presented in the Expected Output, and as a result, all of the solutions are off by 0.007. Now that might not seem like a lot, but if the Value column is in Millions, the Sum of the solutions Total Tax column is 53.803, however when the values are rounded down in Power Query (as they should be if one decimal was Expected Output), the Sum of the Total Tax is 53.9 If the numbers were millions, the underpayment would be $97,000! Sorry, but Mike at ExcelIsFun drilled in how important it was to make accurate calculations. I do understand that this was an exercise in using Power Query, but frankly a 7 step script using simple if/then clauses was much easier to follow, and produced accurate result is what would be required in the real world. Disappointed that only 2 of the posted results actually answered correctly with rounded results - Kgas S (also first post), and CA Satish Panchal.
Excellent video format. The different approaches are amazing. Thank you very much.
So Much of Learning Packed in ~16 minutes.
Thank You!
PQ rocks!!
Only if I'm allowed, a formula alternative solution, no PQ, just to have an idea ( if sales data B1:G15 and tax table I2:K2)
- main calculation
=BYROW(D2:F11*I2:K2*G2:G11,LAMBDA(x,SUM(x)))
- single cell formula including all cosmetics
=LET(t,B1:G11,x,I2:K2,
w,SEQUENCE(ROWS(t)),
b,IFERROR(BYROW(INDEX(t,w,{3,4,5})*x*INDEX(t,w,6),LAMBDA(x,SUM(x))),"Total Tax"),
HSTACK(INDEX(t,w,{1,2,6}),b)
)
Needless to say, sales data can be regular array or table, if table, no refresh ever needed when table receives more data. ✌
Awesome Man
Exceλambda nice!!!
Hi Chandeep,
Always grateful for your publications, let me ask:
Why in the description of your videos, if the topic has been covered in your blog (as in this case) do you not also add the link to the blog page?
Your blogs are very rich in information and failure to include the link could prevent your followers from gaining full knowledge of the topics you share on youtube than on your blog.
Sincerely
An excellent challenge. Thank you for the solutions!
Glad you enjoyed it!
= Table.AddColumn(#"Changed Type", "TotalTaxes",
each
Tax[Tax]{0}* [Tax]*[Value] +
Tax[Tax2]{0}* [Tax2]*[Value] +
Tax[Tax3]{0}* [Tax3]*[Value] )
Hello chandeep sir, great video, I was wondering if we can use "Dynamic M Parameters" to pull the Data within Power BI using SQL Store Procedure, I want to pass SP parameters via Dynamic M query parameters,please assist me any blog would also be fine. Thanks🙏
Great Work!! All the contibutors. They are Amazing!!!
Thank you !
Hi is there a way to separate value of one row into two separate row values where one row is 25% of the value and one cost category while second cost category is 75% of original value...i have solved the problem with 2 separate queryes and appended them with original query where i have filterted the rows so not to duple them..but i assume there is a better solution hehe
Sir Kindly share any YTD/ blogs url links related to Power BI realtime DAX scenarios
kindly make videos on the same. (for practice purpose)
The first solution is my favourite. It feels like the least nested one.
BTW: perhaps you can time these solutions for computational efficiency.
It makes sense time it for very large data sets. Most of them are pretty efficient.
May be I'll do a video on how to measure query performance.
Loved this solution, one question foy anyone, if you change the zero's to null, the method fails
and any row containing a null will return null, can anyone explain or direct me some information as to why this is.
Null is indeterminate like in SQL. So must functions that include a null value will return null
Wish you had posted the code in the blog.
Why calculate the zeros at all? If I was doing this math manually I would ignore them and use a formula to do all calcs at once left to right on the calculator... I would think fewer calcs is more efficient but I am new at all this so probably missing something. Thus the question! Thanks.
I am guessing the cost of checking for and removing the 0 is higher than just letting the calculation run it course. 🙂
I like my solution.
Mohammed.😇
I also like your one with list in list
Great Job!
@@GoodlyChandeep Many thanks Chandeep,
You are the master.
Great Thanks
You are welcome
💯👍
Just to test out the lambda formula
=LAMBDA(taxc,taxc2,taxc3,val,(TEXT(((taxc*Tax[@Tax]*val)+(taxc2*Tax[@Tax2]*val)+(taxc3*Tax[@Tax3]*val)),"0.0")))
Then enter the binary indicator tax columns & values column in the formula
Just watched this again and understand it better, but one thing strikes me (aside from your code missing!). None of the solutions is right! None of them rounded down the Total Tax column to one decimal point as presented in the Expected Output, and as a result, all of the solutions are off by 0.007. Now that might not seem like a lot, but if the Value column is in Millions, the Sum of the solutions Total Tax column is 53.803, however when the values are rounded down in Power Query (as they should be if one decimal was Expected Output), the Sum of the Total Tax is 53.9
If the numbers were millions, the underpayment would be $97,000! Sorry, but Mike at ExcelIsFun drilled in how important it was to make accurate calculations. I do understand that this was an exercise in using Power Query, but frankly a 7 step script using simple if/then clauses was much easier to follow, and produced accurate result is what would be required in the real world.
Disappointed that only 2 of the posted results actually answered correctly with rounded results - Kgas S (also first post), and CA Satish Panchal.
👉🤯👌....wow!
Thanks!