TopN Total Value Issue

[cyborgandy]

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 

[Alef_Ricardo_]

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

[cyborgandy]

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

[Alef_Ricardo_]

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