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.
So I read a thing about Cumulative Sum solutions some time ago, and it did work like a charm.
Here is the link to the solution which I had attemped:
What I did was changed the Nombre to Brands, and Cantidad to Sales Units.
So that I will get Cumulative Sales Units by Brands. (Sorted by Sales Units, from highest to lowest)
But what I need to show is Cumulative Sales Units by number of Brands.
In Table Visual:
What I have: (when Region = X, there are several other regions) What I need:
Brands Sales Units Cumul SU Region Number of Brands
J 50 50 X 1
A 28 78 X 2
M 15 93 X 3
E 5 98 X 4
S 2 100 X 5
What I am trying to show is how are the brands coverage over the market, e.g. 1 brand covers 50% of the market (50/100) in region X, on a Line Chart Visual. I can change the Cumul SU to Percentage of Grand Total, but I don't know how to create the x axis.
Initially I though to make a dynamic measure of ranking for Sales Units, but I ran out of idea of how to.
Edit: I have come up with the solution to show the dynamic ranking, in this case, similar to Number of Brands. But it only works in Table Visual.
Ranking Measure, this gives me the "What I need" above.
Ranking :=
RANKX(
ALLSELECTED('Data'[Brands]),
RANKX(
ALLSELECTED('Data'[Brands]),
CALCULATE(SUM('Data'[Sales Units]))
), , ASC )
Unfortunately, I can't visual these numbers as X axis in Line Chart Visual, I could put in on the tooltip, but what I want is to put it as X axis.
Any suggestions? Thank youu!!
Solved! Go to Solution.
Hi @Anonymous
I'm not sure whether you want a calculated column in your table or a table visual. I've assumed the latter for now. Try this, where Table1 is the table you show without the 'Number of Brands' column
1. Set Brands, Sales Units, Cumul SU and Region region in a table visual. Make sure all of them are set to 'Don't summarize'.
2. Create this measure and place it in the visual
Measure = RANKX ( CALCULATETABLE ( Table1; ALLEXCEPT ( Table1; Table1[Region] ) ); Table1[Sales Units]; SELECTEDVALUE ( Table1[Sales Units] ); DESC )
Hi @Anonymous
I'm not sure whether you want a calculated column in your table or a table visual. I've assumed the latter for now. Try this, where Table1 is the table you show without the 'Number of Brands' column
1. Set Brands, Sales Units, Cumul SU and Region region in a table visual. Make sure all of them are set to 'Don't summarize'.
2. Create this measure and place it in the visual
Measure = RANKX ( CALCULATETABLE ( Table1; ALLEXCEPT ( Table1; Table1[Region] ) ); Table1[Sales Units]; SELECTEDVALUE ( Table1[Sales Units] ); DESC )
Hi @AlB , thanks for the reply.
Sorry, I should had mentioned it in the post. I planned to show it with the Line Chart Visual.
So the X axis will be: number of Brands, and
Y axis will be: Cumul SU (%)
But I will try to work from your solution first, maybe I will get a better presentation. Thanks a lot!!
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |