topic TopN Total Value Issue in DAX Commands and Tips

TopN Total Value Issue

cyborgandy — Mon, 14 Aug 2023 11:49:07 GMT

Hi All,

I have created a dynamic topn slicer, I am expecting a result if someone slects top 10 or top 20, in a card it should show the total spend of those supplier, however in my case its showing the total spend of the whole spend of selected calendar period.

Below is the dax I writtent, Please help me.

Top N Rank = IF([Supplier Rank]<='Top N Suppliers'[Top N Suppliers Value],[Total Spend],BLANK())

Top N Suppliers Value = SELECTEDVALUE('Top N Suppliers'[Value], 10)

Total Spend = CALCULATE(SUM(' Spend Details'[Amount]))

Supplier Rank = RANKX(ALL('Spend Details'[Supplier name]),[Total Spend],,DESC)

In the below snashot the total spend ss showing for the selected period , I want it should show the spend of top 10 only on grandtotal for selected calendar period

Re: TopN Total Value Issue

Alef_Ricardo_ — Wed, 16 Aug 2023 01:28:58 GMT

It seems that the issue you are facing is that the total spend is showing for the selected period, instead of showing the spend of the top 10 suppliers only.

One possible solution to this issue is to modify your Total Spend measure to only include the top N suppliers based on the value selected in the Top N Suppliers slicer. You can do this by using the TOPN function in your CALCULATE statement, like this:

Total Spend =
VAR TopNSuppliers = TOPN('Top N Suppliers'[Top N Suppliers Value], ALL('Spend Details'[Supplier name]), [Total Spend])
RETURN
CALCULATE(SUM('Spend Details'[Amount]), TopNSuppliers)

This measure calculates the total spend by summing the Amount column from the 'Spend Details' table, but only for the top N suppliers based on the value selected in the Top N Suppliers slicer. The TOPN function returns a table containing the top N rows from the 'Spend Details'[Supplier name] column, ranked by the [Total Spend] measure in descending order.

I hope this helps! Let me know if you have any further questions or if there’s anything else I can assist you with. 😊

Re: TopN Total Value Issue

Alef_Ricardo_ — Wed, 16 Aug 2023 01:29:27 GMT

Sure, I can help you fix the issue with your DAX code. Here’s the corrected version of your code:

Top N Rank =
IF(
[Supplier Rank] <= 'Top N Suppliers'[Top N Suppliers Value],
[Total Spend],
BLANK()
)

Top N Suppliers Value =
SELECTEDVALUE('Top N Suppliers'[Value], 10)

Total Spend =
VAR TopNSuppliers = TOPN('Top N Suppliers'[Top N Suppliers Value], ALL('Spend Details'[Supplier name]), [Total Spend])
RETURN
CALCULATE(SUM('Spend Details'[Amount]), TopNSuppliers)

Supplier Rank =
RANKX(
ALL('Spend Details'[Supplier name]),
[Total Spend],
,
DESC
)

In this version, the Total Spend measure has been modified to only include the top N suppliers based on the value selected in the Top N Suppliers slicer. This is done by using the TOPN function in the CALCULATE statement to return a table containing the top N rows from the 'Spend Details'[Supplier name] column, ranked by the [Total Spend] measure in descending order.

I hope this helps! Let me know if you have any further questions or if there’s anything else I can assist you with. 😊

Re: TopN Total Value Issue

cyborgandy — Wed, 16 Aug 2023 05:38:26 GMT

Thanks for your response, but I want this ti also interact with the date range slicer alongwith dynamic topn slicer, could you please help