topic Re: Pareto values used in a text box (scalar values) in DAX Commands and Tips

Pareto values used in a text box (scalar values)

datadonuts — Mon, 24 Jan 2022 18:29:14 GMT

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 =

CALCULATE([Paretotest Total Sales],

FILTER(

ALL(

ParetoTestData[Customer]),

sum(ParetoTestData[Amount]) <= [Paretotest Total Sales]))

Pareto % RT

Paretotest RT % simple = DIVIDE([ParetoTest RT simple],[Paretotest total All Sales],0)

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

https://app.powerbi.com/view?r=eyJrIjoiODQzOWJiZDItODY2Yy00Y2UwLTlmNDQtY2E3ZDk4NGIwOGU2IiwidCI6IjU0OGVmYThjLWI1MzEtNDRjOS05MGY4LTAzZDc4MTdkMzdmZSJ9

Re: Pareto values used in a text box (scalar values)

Whitewater100 — Mon, 24 Jan 2022 19:41:20 GMT

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.

Card Title =

VAR location = SELECTEDVALUE(Locations[State Code])

VAR customercount = COUNTROWS(Customers) * .2

var paretosales = [Sales of Top 20% of Customers]

var perctsales = [% of Sales]

return

"IN StateCode " & location & " " & customercount & " (20% of customers) represent "

&FORMAT( paretosales, "Currency")&" USD ("&FORMAT( perctsales, "Percent")& "of Sales)"

I could not get to your data so I used something I had with states. The measure result looks like this:

I'm hoping the foundation is here for you to adjust. Hope this helps!

Re: Pareto values used in a text box (scalar values)

datadonuts — Tue, 25 Jan 2022 18:20:23 GMT

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

Microsoft Power BI

Hope you can help me with that

Best Regards

Benedikt

Re: Pareto values used in a text box (scalar values)

v-janeyg-msft — Thu, 27 Jan 2022 07:51:07 GMT

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

Re: Pareto values used in a text box (scalar values)

datadonuts — Thu, 27 Jan 2022 12:53:39 GMT

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.

https://app.powerbi.com/view?r=eyJrIjoiODQzOWJiZDItODY2Yy00Y2UwLTlmNDQtY2E3ZDk4NGIwOGU2IiwidCI6IjU0OGVmYThjLWI1MzEtNDRjOS05MGY4LTAzZDc4MTdkMzdmZSJ9

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.

summurize country 80% =

VAR totalsales80 = [Total All Sales]*0.8

VAR countryRT80table =

FILTER(

ADDCOLUMNS(

SUMMARIZECOLUMNS(

customers[Country],fOrders,"totalsales",[Total Sales]),

"ranksalesbyc", AllMeasures[rank countries by sales],

"countryRT", AllMeasures[country Sales RT])

[countryRT]<=totalsales80)

RETURN

max([countryRT])

Best Regards

Benedikt

Re: Pareto values used in a text box (scalar values)

v-janeyg-msft — Fri, 28 Jan 2022 11:53:06 GMT

datadonuts

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

Re: Pareto values used in a text box (scalar values)

datadonuts — Fri, 28 Jan 2022 18:34:19 GMT

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.

Re: Pareto values used in a text box (scalar values)

v-janeyg-msft — Tue, 01 Feb 2022 01:43:49 GMT

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

Re: Pareto values used in a text box (scalar values)

datadonuts — Sat, 05 Feb 2022 22:50:54 GMT

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

Summarize customerID selected country 80% salesvalue =

VAR selectedcountry = SELECTEDVALUE(customers[Country])

VAR totalsales80country = CALCULATE([Total Sales]*0.8, FILTER(fOrders,fOrders[ShipCountry]=selectedcountry))

VAR tablecustomerRT80 =

FILTER(

ADDCOLUMNS(

SUMMARIZECOLUMNS(

customers[CustomerID],customers[Country],

fOrders,"totalsales",[Total Sales]),

"ranksalesbycid", AllMeasures[rank custID by sales],

"customerIDRT", AllMeasures[custID Sales RT]),

[customerIDRT]<=totalsales80country && [Country]=selectedcountry)

RETURN

MAXX(tablecustomerRT80,[customerIDRT])

Thanks for your great help!