Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Dear Power BI Community!
I am new to the tool and yet I am challenged by following issue.
I want to show how much of out total sales revenue does our top 20 best-buying customers make. Looks like pareto.
1. I ranked TOP 20 customers in cluster bar chart filtered by (limited to) Top 20 revenues.
2. Now I need to provide the number what is a % of this TOP20 in our total sales. I like to show in on "Card" visual.
3. This might be important: the Total Sales will be also be sliced into countries of sales. So I would need ex. % of TOP 20 customers of US in US total sales, etc in other countries.
Would you be so nice and help me figuring out the measure that will answer my question, please?
Kind regards,
Alex
Hi @b7laska,
Did my solution work?
Best Regards,
Dale
Hi @b7laska,
There isn't a native or custom pareto chart in Power BI (how I wish there was one!). There are tutorials on how to make one though. The steps are a little finnicky especially for those who have just started using the tool. @v-jiascu-msft has already mentioned a link to a tutorial. Here's another one - https://powerbi.tips/2016/10/pareto-charting/
Proud to be a Super User!
Hi Alex,
Can you share a dummy sample please? It's hard to figure out measures without data and its structure. Please refer to these pareto related topics.
create-a-pareto-chart-in-power-bi/
http://visualbi.com/blogs/powerbi/pareto-analysis-power-bi/
Best Regards,
Dale
Absolutely, Dale.
Please, find the screenshot of TOP 20 customers (filtered in manual filter TOP N). I want to show what % of total sales they make.
Then, I would like to be able to provide that information even if I slice the data into localisation. Meaning, I need to show how much of TOP 20 customers in Australia make % total Australian Sales. So the same mechanism, but different denominator (slicer-dependant).
I would appreciate your help and time
Thanks
Alex
Hi Alex,
We can't create a solution without data. But there is a demo in the attachment. Please check it out.
1. In order to verify the formula, we can try this measure.
Top20IDs = CONCATENATEX ( TOPN ( 20, DimGeography, CALCULATE ( SUM ( FactSales[SalesQuantity] ) ), DESC ), [GeographyKey], "-", [GeographyKey], DESC )
2. The formula for % of total sales is as follows.
top20% = VAR top20Total = SUMX ( ADDCOLUMNS ( TOPN ( 20, DimGeography, CALCULATE ( SUM ( FactSales[SalesQuantity] ) ), DESC ), "Sales", CALCULATE ( SUM ( FactSales[SalesQuantity] ) ) ), [Sales] ) RETURN DIVIDE ( top20Total, SUM ( FactSales[SalesQuantity] ), 0 )
Best Regards,
Dale
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |