DAX measure help: Filter on division, split by subdivision and year

[Cerwan]

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

[DataInsights]

@Cerwan,

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

[Cerwan]

@DataInsights 

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. 🙂

[DataInsights]

@Cerwan,

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

[Cerwan]

@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.