Forum Discussion
TopN Total Value Issue
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. 😊
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. 😊