Forum Discussion
Help with DAX formula required please
I need some DAX help please.
I have created a matrix visual of revenue by client (rows) and services (columns) - no problem there.
I have created another matrix which calculates the total value of my Top N clients, and the proportion of the Grand Total these Top N represent, again with the services as columns (so Total Revenue and % of Total as rows).
My problem is my measure to calculate the Top N revenue calculates the Top N based on the Top N values for each of the respective services whereas I want it to be based on the Top N clients' total revenue.
In the example below, my measure calculates the Top 3 for service A by totalling the 3 highest values found for clients with A services. However, I want to total service 'A' values of the 3 highest clients based on the Total column.
Here's my current measure
(where [Revenue] is just a SUM() of the revenue field, and [Top N] is a measure returning the # of Top N clienst to sum for, eg 10):
Finally I have solved this. With some help from SQLBI's tutorial on Top N, I modified my TOP N formula, and had to clear the column filter for Service which the matrix was applying.
Here's my DAX for anyone interested.Revenue Top N =
CALCULATE (
[Revenue],
TOPN (
[Top N],
ADDCOLUMNS (
ALLSELECTED(Data[Client Name]),
"Total Revenue",
//calc revenue for all Services by clearing the Service column filter
CALCULATE(
[Revenue],
ALLSELECTED(Data[Service])
)
),
[Total Revenue], DESC,
Data[Client Name], ASC
)
)
3 Replies
- amitchandak
Super User
Anonymous , Based on what I got. Try % measure like
divide(calculate([Revenue],TOPN([Top N],allselected(Data[Client Name]),[Revenue],DESC), values(Data[Client Name]))
, calculate([Revenue],TOPN([Top N],allselected(Data[Client Name]),[Revenue],DESC))) Thanks for your suggestion amitchandak.
However, I need to get the revenue number correct as it's displayed (and I can then calculate the % of total from it and another measure I already have working).
I did notice you've used ALLSELECTED()... I've not used that before so have used it instead of my ALL (after researching the function) however it didn't help.
Finally I have solved this. With some help from SQLBI's tutorial on Top N, I modified my TOP N formula, and had to clear the column filter for Service which the matrix was applying.
Here's my DAX for anyone interested.Revenue Top N =
CALCULATE (
[Revenue],
TOPN (
[Top N],
ADDCOLUMNS (
ALLSELECTED(Data[Client Name]),
"Total Revenue",
//calc revenue for all Services by clearing the Service column filter
CALCULATE(
[Revenue],
ALLSELECTED(Data[Service])
)
),
[Total Revenue], DESC,
Data[Client Name], ASC
)
)
