Hello @Harsh ,
I hope you had a great weekend.
I watched both Summits and read the article.
I agree that DAX is simple but not easy. With DAX you need to understand how each function will behave it’s not like excel where you can dive into the formula bar and read each step by step.
I think slowly I start to understand how the advanced RANKX formula works, as I understood the formula is summing up so we need to be careful if there are any duplicate names.
I am attaching the file that I work with. Pareto.pbix (122.9 KB)
There are few customers that have the same name but unique customer ID.
I have used different contexts to ensure I will get the correct answer.
Page 1 I Ranked by Customer and By Customer ID to display customerID and customer names, I add ALLSelected so it’s applying extra context.
On-Page 2 I add another context City, but then while I review the data, the rankx worked nearly correct, the data was kind of duplicating creating extra customer names for each City. I believe I could merge all 3 and then Rank them by unique name then the formula would work as I assume.
I used If statement to rank everything in order but I run into a small issue
If Rank City/Customer/ID = IF([Total Sales] = 0,BLANK(),CALCULATE( RANKX(ALL(Customer[Customer ID]), [Total Sales],DESC),
Is it possible to adjust the if statement to put 0 value in rank order? Or I should actually for this task merge 3 columns in the sales table since City and Customer are in two different tables?
On page 3 I tried to calculate 20% of RANKX
To ensure it works correctly, I applied the TOPN formula if you notice both Column Charts are correct.
But when we apply customer name or city context to the table I don’t believe the formula works correctly. As I do know in City 2 (see matrix table) there are sales over 6.5 and there is one customer only.
To see my TOP20% customer, I just used TOPN formula (see TOP27)
I thought I can apply Variable function to my top20% but I just received a correct total amount but when the customer names context is applied there rows are empty. I tried to apply your Formula “ISfiltered” but didn’t work.
I divided your formula into two sections to have a better understanding and this is how I understand:
At Page 4 I played with grouping measures
If I apply the filter for the city then I get all customer names grouped correctly but if apply the filter from treemap only one customer is visible.
Should I turn off the interactions between grouping and treemap or the formula needs to be adjusted?
As I understand treemaps are good for showing the proportions between each part and the whole, display large numbers shows patterns, outliers.
I will appreciate your help.