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 community,
hope someone can help me before my head explodes. I searched for days now, but could not find an answer.
Here is the challenge:
I am working on a pareto chart, which wasn't that complicated to create a table and a chart out of it (see attached PBIX with demo data), thanks to some awesome guys and their videos.
Pareto Running Total
ParetoTest RT simple =
Now I want to put this information in a text box saying:
"In the country XYZ, n out of m customers (20%) made A USD (80% of total sales of B USD)"
The easy ones are:
m = all customers (in this country)
B = all sales in this country
But I also need the scalar values for n and A:
n = Count of customers which represent
A = 80% of the sales (and this value should be the calculated pareto running total, not simply 80% of the sales)
In my example the text card should say:
"In country XYZ 2 customers (20% of customers) represent 35300 USD (81.0% of sales)".
Please ignore the country selection for now to make it as simple as possible.
Hope someone really smart can help me ..... please
Thanks Benedikt
Solved! Go to Solution.
I think your explanation is really funny and puzzling. And what you've been sharing is not a pbix file but a webpage...
Since I don't know what exactly you want, I created a summarize table that contains all your needs. This way you can easily display whatever you want in the card.
I'm taking some days off. I hope you can take a good look at my measure.
Did I answer your question? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.
Best Regards,
Community Support Team _ Janey
Hi Benedikt!
I beleive this works, you just need to substitute your table -measure names in place of mine. This will produce a dynamic title after you select your customer from your table/matrix.
I'm hoping the foundation is here for you to adjust. Hope this helps!
Thank you so much for your reply, but I obviously did not express my question clearly. I do not need help with the dynamic text, that's a piece of cake. I need to calculate the values, which go into it, in your case
var paretosales = [Sales of Top 20% of Customers] <- how to calculate this value?
var perctsales = [% of Sales] <- how to calculate that?
This is the data
Pareto Test | |||
1 | Customer A | 400 | Germany |
1 | Customer A | 300 | Germany |
2 | Customer B | 6700 | USA |
2 | Customer B | 10400 | USA |
2 | Customer B | 6700 | USA |
3 | Customer C | 2300 | USA |
3 | Customer C | 400 | USA |
4 | Customer D | 100 | UK |
4 | Customer D | 100 | UK |
5 | Customer E | 500 | Germany |
6 | Customer F | 600 | UK |
7 | Customer G | 800 | Germany |
7 | Customer G | 1400 | Germany |
9 | Customer I | 4600 | UK |
9 | Customer I | 2200 | UK |
9 | Customer I | 3700 | UK |
8 | Customer H | 800 | USA |
9 | Customer I | 1000 | UK |
10 | Customer K | 600 | UK |
Hope you can help me with that
Best Regards
Benedikt
Hi, @datadonuts
I read your previous reply, I believe your needs can be achieved, but I really don't understand what you want to calculate...Your needs are incomplete.
[Sales of Top 20% of Customers]: Calculate the sum ??of the Top 20% of Customers, Top 20% of Customers is total or by country? [% of Sales] also.
If you still need help, please share some sample data and your calculate logic and your desired result. Waiting for your addition.
Best Regards,
Community Support Team _ Janey
Hi Janey,
thank you so much for replying. I will try to express myself more clearly:
As it is pretty staight forward to calculate the Running Total and Running Total Percentage in a table visual, I want to place the "80% values" as a SCALAR value in a card visual. This values shall return the following
The running total sales of the customers representing <= 80% of the sales (in the example the number is 35300)
The count of customers representing <= 80% of the sales (in the example the number 2 (bc the count is 2 customers represent this sales equals to 81% of running total sales ))
For this pareto example (20/80) it means that 2 customers (our of x all customers) represent 81.0% of the sales, in total 35300 USD of the total sales of 43600 USD. These numbers shall be placed either in 3 card visuals or in a text visual adding the values.
Please see attched picture
Hope that gives you more clarity of what I want to achieve
I placed the PBIX file here as well the data I used for it.
CustomerIDCustomerAmountCountry
Pareto Test | |||
1 | Customer A | 400 | Germany |
1 | Customer A | 300 | Germany |
2 | Customer B | 6700 | USA |
2 | Customer B | 10400 | USA |
2 | Customer B | 6700 | USA |
3 | Customer C | 2300 | USA |
3 | Customer C | 400 | USA |
4 | Customer D | 100 | UK |
4 | Customer D | 100 | UK |
5 | Customer E | 500 | Germany |
6 | Customer F | 600 | UK |
7 | Customer G | 800 | Germany |
7 | Customer G | 1400 | Germany |
9 | Customer I | 4600 | UK |
9 | Customer I | 2200 | UK |
9 | Customer I | 3700 | UK |
8 | Customer H | 800 | USA |
9 | Customer I | 1000 | UK |
10 | Customer K | 600 | UK |
I am now a step further but still not there where I want. Check out what I figured, maybe it gives you an idea, where I am going to with it.
I think your explanation is really funny and puzzling. And what you've been sharing is not a pbix file but a webpage...
Since I don't know what exactly you want, I created a summarize table that contains all your needs. This way you can easily display whatever you want in the card.
I'm taking some days off. I hope you can take a good look at my measure.
Did I answer your question? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.
Best Regards,
Community Support Team _ Janey
Dear Janey, here I am back. Thanks for your great solution. I adopeted it in order to select also a country in the measure. So the measure looks like this
Hi Janey, your solution is well rceived. some DAX is pretty extensive for me, so even it looks to be the result expected, I also want to understand, how it comes to be. Please give me a day or two to review your DAX before I can tell, thats really the solution I am looking for. For now, thanks a lot.
Hi @datadonuts
Because the data you want is virtual and can ‘t be calculated with calculated columns, and it needs to be displayed in the card, there is no context in the card, so it can only be displayed by creating a context by building a virtual table. If there is something you don't understand, you can ask me.
SUMMARIZE function (DAX) - DAX | Microsoft Docs
ADDCOLUMNS function (DAX) - DAX | Microsoft Docs
RANKX function (DAX) - DAX | Microsoft Docs
Best Regards,
Community Support Team _ Janey
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 |
---|---|
55 | |
26 | |
23 | |
15 | |
9 |
User | Count |
---|---|
77 | |
61 | |
47 | |
17 | |
12 |