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,
I have been searching around the forum and trying out a few solutions for similar problems, but none of them seemed to do the trick completely for me. In Excel, the calculation takes only a few seconds, in Power BI, it proves to be a little more complex. I have created fictive tables and numbers for extra clarity here (removed superfluous info etc). All measures need to be performed in the same table.
I'll try to sketch the situation as thoroughly as possible:
I have a dataset of customer sales information, and I have been able to subract the information above in tables and matrixes separately in Power BI. However, now I need to compare the totals as a percentage: i.e. which percentage of the total sales is bought by the top 3 customers?
It seems impossible to use data from calculated tables on the desktop, and I do not succeed in recreating all the steps in measures and columns.
all I need to do is create a formula to divide the sum of the sales total of the top 3 customers by the total of all sales. I tried to use the DAX 'TOPN' function, but I do not succeed of making a ranking of the customers based on the sales amounts.
it's probably some basic formula I'm missing here, but I have only started working with DAX and Power BI recently, and I'm missing some basic knowledge to fully understand and analyze similar problems posted on this forum before, let alone to project those solutions to my problem.
Thanks!
Solved! Go to Solution.
HI @BVdL,
Create two measures:
ranking = RANKX ( ALLSELECTED ( Sales[Customer] ), CALCULATE ( SUM ( Sales[Qt] ) ) ) Top 3 % = DIVIDE ( CALCULATE ( SUM ( Sales[Qt] ), FILTER ( ALLSELECTED ( Sales[Customer] ), [ranking] < 4 ) ), CALCULATE ( SUM ( Sales[Qt] ), ALLSELECTED ( Sales[Customer] ) ) )
You can change the 4 by any number you want and can even make it with a user selection if needed.
Final result below:
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHI @BVdL,
Create two measures:
ranking = RANKX ( ALLSELECTED ( Sales[Customer] ), CALCULATE ( SUM ( Sales[Qt] ) ) ) Top 3 % = DIVIDE ( CALCULATE ( SUM ( Sales[Qt] ), FILTER ( ALLSELECTED ( Sales[Customer] ), [ranking] < 4 ) ), CALCULATE ( SUM ( Sales[Qt] ), ALLSELECTED ( Sales[Customer] ) ) )
You can change the 4 by any number you want and can even make it with a user selection if needed.
Final result below:
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThanks a lot, this worked like a charm!
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 |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |