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.
Hello Folks,
I'm trying to create measure for Top 10 customers based on sales amount in Power BI which is connected Live to the Tabular model.
Customer is Dimension and Sales amount is in fact table and I need to Rank the customers from 1 to 10 based on highest sales amount for each customer.
I have tried to create this DAX query below but this gives me Rank '1' for all the rows, I am guessing this has to do with filter context.
Solved! Go to Solution.
Hi @sqlguru448 ,
Be aware that making a summarizaition of a full table will bring for sure performance issues, in your case you are using the FACT table that I assume have a huge number of data so be aware of that.
Try the following code:
Measure =
var temp_table =
FILTER (
SUMMARIZE (
ALLSELECTED ( Customer[Name] );
Customer[Name];
"Total_Sales"; [Sales Amount]
);
[Sales Amount] > 0
)
var Ranking = RANKX(temp_table; [Sales Amount])
RETURN
Ranking
Then filter out all rankings above 10.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHello @sqlguru448 ,
What is the expected result? Can you share a sample data/pbix file along with the expected results?
Cheers!
Vivek
Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter
Hi @vivran22
Below is the sample data screenshot. The ranking should be from 1 to 10 based on highest sales amount. but my above dax gives me Rank 1 in all rows.
Hi,
Try this measure
=rankx(all(data[Customer]),[Sales])
I have assumed that sales is an explicit measure.
Hope this helps.
This has performance issue when other dimesions are added to the visual, that is why I am trying to get top 10 first within variable table and then add other dimensions to it. Thank you.
HI @sqlguru448
Try to use this formula to create a measure
Measure 2 =
VAR _RANK= RANKX (
ALLSELECTED ( Customer[Name] ),
CALCULATE ( SUM ( 'Fact'[Sales Amount] ) ))
RETURN
IF ( _RANK <= 10, _RANK)
if not your case, please share some simple sample data and your expected output.
Regards,
Lin
Just came out today check this videos about rankx and countrows.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi
@v-lili6-msft The above DAX gives performance issues as posted in my earlier comments as my real data is in millions, I am adding other dimension columns to the table which is causing the issue. Below is the expected data result for rank, apart from this I have to add other columns from different dimensions.
I am trying to fix it by writing below DAX, this works fine in SSAS dax query but not in Power BI desktop.
The power bi desktop gives rank as all 1's
Hi @sqlguru448 ,
Be aware that making a summarizaition of a full table will bring for sure performance issues, in your case you are using the FACT table that I assume have a huge number of data so be aware of that.
Try the following code:
Measure =
var temp_table =
FILTER (
SUMMARIZE (
ALLSELECTED ( Customer[Name] );
Customer[Name];
"Total_Sales"; [Sales Amount]
);
[Sales Amount] > 0
)
var Ranking = RANKX(temp_table; [Sales Amount])
RETURN
Ranking
Then filter out all rankings above 10.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThank you this gave me an idea to modify my dax.
Hi @sqlguru448 ,
Glad I could point out in the correct direction, I made the measure with only a few lines of data so it could work incorrectly on a bigger scale.
Always remenber that FILTERING a full table will have a very big impact on performance, you should always filter on the minimum amount of columns needed for your calculations and your context needs.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering 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 |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |