Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Greetings all. 🙂
I'm a rookie, when it comes to DAX and even though I'm taking both courses, reading the Definite Guide and practicing, I have a task, which unfortunately cannot wait for me to get proficient enough, to solve it on my own.
I have a data-model with one table in it (not my choice), with the following important columns:
Department, division, subdivision, unit, month & year of expense, supplier name, expense amount.
I need a measure to supply me with the Division Top20 suppliers based on expense amount (filterable by the month & year).
The trick is, that I then need to divide each divisions Top20 suppliers onto the subdivisions and units of that division.
So for each division, I need to show how much of that Top20 spend, each of their subdivisions and units have.
I'm currently using a matrix with supplier, subdivision and unit as rows, and expense amount (and other amounts) as values.
Then I have a slicer on division and a slicer on the month & year ("mmm yy").
I've filtered the matrix with the Top20 suppliers based on expense amount, and it seems to be working with the current row-configuration. (With suppliers on top and subdivision & unit as lowerlevel rows)... However, I need to have subdivisions as top-row and then the filter works on the subdivision-level, not the division level. Hence my need for a division-level measure.
Note: I am using live-data, so I cannot use calculated columns (the options are grayed out for me). Everything has to be in a measure, unfortunately.
Is there a way to do this dynamically? I.e. without using a fixed division-name as the filter for each measure, but using a slicer to determine the division-name on which, the top20 is filtered?
Sadly, I cannot load a datasample, as that would be a breach of protocol here... But if needed, I'll create something.
I hope, you can help. 🙂
Regards, Emiel
Try these measures:
Expense Total = SUM ( Table1[Expense Amount] )
Top 20 Suppliers =
// get the top 20 Suppliers for the current Division
VAR vTopRows =
CALCULATETABLE (
TOPN ( 20, VALUES ( Table1[Supplier Name] ), [Expense Total] ),
ALLSELECTED ( Table1 ),
VALUES ( Table1[Division] )
)
// calculate Expense Total in the context of the top 20 Suppliers
VAR vResult =
CALCULATE ( [Expense Total], vTopRows )
RETURN
vResult
Proud to be a Super User!
Thank you very much! It is almost working perfectly. 🙂
The only thing: If I show more than one division in my matrix, the measure calculates the Top20 suppliers across all shown/selected divisions, not the top20 for each division.
Is there a way to modify the measure, so if I have several divisions showing, it will calculate the top20 for each division seperately?
Otherwise I will mark your answer as the solution and be happy with, what I got. 🙂
Would you be able to create a mock-up of the scenario? It sounds like the Division slicer would have multiple values selected. You could try using SUMMARIZE instead of VALUES:
Top 20 Suppliers =
// get the top 20 Suppliers for the current Division
VAR vTopRows =
CALCULATETABLE (
TOPN ( 20, SUMMARIZE ( Table1, Table1[Division], Table1[Supplier Name] ), [Expense Total] ),
ALLSELECTED ( Table1 ),
VALUES ( Table1[Division] )
)
// calculate Expense Total in the context of the top 20 Suppliers
VAR vResult =
CALCULATE ( [Expense Total], vTopRows )
RETURN
vResult
Proud to be a Super User!
@DataInsights
I'll make a mock-up ASAP, thank you. 🙂
Sadly, summarize didn't change the situation, but I'm still closer, than I've ever been. 🙂
And yes, I haven't forced the user to single-select a division in the slicer, as some of our business partners have more than one division in their portfolio.
I'll tag you, when I have the mock-up...
And thank you again for helping. I appreciate it a lot.
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 |
---|---|
42 | |
21 | |
21 | |
14 | |
14 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |