topic Re: DAX measure help: Filter on division, split by subdivision and year in DAX Commands and Tips

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

Anonymous — Mon, 30 Jan 2023 08:59:28 GMT

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

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

DataInsights — Mon, 13 Feb 2023 14:15:06 GMT

Anonymous,

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

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

Anonymous — Mon, 13 Feb 2023 15:49:42 GMT

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

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

DataInsights — Mon, 13 Feb 2023 17:17:16 GMT

Anonymous,

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

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

Anonymous — Tue, 14 Feb 2023 08:17:17 GMT

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.