Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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):
Solved! Go to Solution.
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.
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.
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.
@pjandliz , 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)))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
98 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |