Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Having trouble with my DAX.
Table contains different Account charges for different periods.
Having a measure to calculate the amont only for the Sales and calculating the % for each account against the sales .
But, the total is going wrong for the below measure. This is my first attempt with ALL & ALLSELECTED.
Pbix is https://drive.google.com/file/d/19MJftynUVzV9BVfU3RuV0RLeKHSJ6K_G/view?usp=sharing
Solved! Go to Solution.
@Pbi07 change sales total measure to this and everything will work
Sales Total =
CALCULATE( SUM('Sales'[Amount] ),
'Sales'[Account] = "Sales",
ALLSELECTED ( Sales )
)
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hi, it seems you want slicer filter to flow into your Total Sales, try it:
Sales Total 2:=CALCULATE( SUM('Sales'[Amount] ),
FILTER( ALL('Sales'[Account]),'Sales'[Account] = "Sales")
)
Thanks @Vera_33
That works for the total with the below
Sales Total 2:=CALCULATE( SUM('Sales'[Amount] ),
FILTER( ALL('Sales'[Account]),'Sales'[Account] = "Sales")
)
But, when i use this total to find the % of Sales it gives 0 for all the rows. That was the reason i was trying to do the ALLSELECTED.
What am i missing
Account | Location | Total Amount | Last Year Total | % of Sales |
Materials | EU | $95,325 | $17,325 | 0 |
Packing | AP | $55,000 | 0 | |
Packing | US | $16,690 | $19,420 | 0 |
Oh...I see, the % is a little bit tricky...you want to get the sales amount but keep the slicer filter flowing into the denominator as well? Try this:
Sales Total 2=
VAR CurYear = SELECTEDVALUE('Date'[Year],ALL('Date'[Year]) )
RETURN
CALCULATE( SUM('Sales'[Amount] ), FILTER(ALL(Sales), Sales[Year]=CurYear && Sales[Account]="Sales")
)
@Pbi07 it is not clear what you are looking for, given the example what is your expected output, explain what is working and what is not working?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Is this what you are looking for?
I just changed the sales measure to ignore the selected account (on the row) and always return the amount for sales.
Sales =
CALCULATE(
[Total Amount],
'Sales'[Account] = "Sales"
)
@Pbi07 ,Try like
Sales Total =
CALCULATE( SUM('Sales'[Amount] ),All('Sales'))
% = divide( SUM('Sales'[Amount] ),[Sales Total])
The total is only for the [Account] = "Sales". My slicer is Fiscal Year. I have the % calculation working. My date table has relation set to the Period End Date.
Journal | Fiscal Year | Fiscal Period | Account | Location | Period end Date | Amount |
5150 | 2019 | 9 | Materials | EU | 1/31/2019 0:00 | $1,800.00 |
5870 | 2019 | 12 | Materials | EU | 4/30/2019 0:00 | $15,525.00 |
10202 | 2020 | 1 | Materials | EU | 5/31/2019 0:00 | $48,000.00 |
34842 | 6060 | 36 | Materials | EU | 4/30/2020 0:00 | $47,325.00 |
11440 | 2020 | 11 | Packing | AP | 3/31/2020 0:00 | $55,000.00 |
5209 | 2019 | 10 | Packing | US | 2/28/2019 0:00 | $1,720.00 |
5590 | 2019 | 12 | Packing | US | 4/30/2019 0:00 | $17,700.00 |
10275 | 2020 | 3 | Packing | US | 7/31/2019 0:00 | $16,690.00 |
90020 | 2020 | 11 | Sales | EU | 3/31/2020 0:00 | $428,868.00 |
80006 | 2019 | 11 | Sales | US | 3/31/2019 0:00 | $185,000.00 |
90001 | 2020 | 10 | Sales | US | 2/29/2020 0:00 | $188,111.00 |
@Pbi07 , need expected output. Check if this can help
https://www.dropbox.com/s/k0opque79mpx0n6/sales_percent.pbix?dl=0
@amitchandak @parry2k @jdbuchanan71
Apologies for not providing the expected result.
Account | Location | Total Amount | Last Year Total | % of Sales |
Materials | EU | $95,325 | $17,325 | 15% |
Packing | AP | $55,000 | 9% | |
Packing | US | $16,690 | $19,420 | 3% |
% of sales = Total Amount / 616979 . 616979 is the total of "Sales" for 2020 FY.
@Pbi07 change sales total measure to this and everything will work
Sales Total =
CALCULATE( SUM('Sales'[Amount] ),
'Sales'[Account] = "Sales",
ALLSELECTED ( Sales )
)
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
102 | |
86 | |
77 | |
69 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |