topic Re: How to add a measure's result of many lines in DAX Commands and Tips

How to add a measure's result of many lines

Anonymous — Wed, 09 Nov 2022 12:00:22 GMT

Hi everyone!

I am currently working on a table that relates the amount of components that my company receives and returns.
Here is how the table is at this moment:

"Cod. Forn. Principal" is an ID.

"Entradas_bus" are the received components.
"Refugos_bus" are the components we have returned.
"Impacto_PPM" is calculated by a measure. It's the returned pieces of one ID divided by the SUM() of all received pieces during an interval of time.

"Porcentual PPM" is the % that each line represents in the total of 'Impacto_PPM'. Basically, I want to know how much % does 829,27 represents out of the total 'Impacto_PPM'.

Right now I'm struggling to calculate the "Porcentual PPM" because I can't find a way to get the summation of all lines in the "Impacto_PPM" column.

can somebody help me please

Re: How to add a measure's result of many lines

FreemanZ — Wed, 09 Nov 2022 14:48:23 GMT

Supposing your table named Table, try to add a new column with the code below:

Porcentual PPM = FORMAT ( DIVIDE( CALCULATE(SUM('Table'[ Impacto_PPM])), SUM('Table'[ Impacto_PPM])), "00%")

Re: How to add a measure's result of many lines

johnt75 — Wed, 09 Nov 2022 14:55:03 GMT

Try this as the measure

Porcentual PPM = DIVIDE ( [Impacto PPM], CALCULATE ( [Impacto PPM], ALLSELECTED () ) )

Re: How to add a measure's result of many lines

Anonymous — Wed, 09 Nov 2022 19:33:00 GMT

First of all, thanks for trying to help me out!
But unfortunately it doesn't work, the numbers don't add up to 100% 😞

Re: How to add a measure's result of many lines

Anonymous — Wed, 09 Nov 2022 19:39:55 GMT

Hi FreemanZ, thanks for helping!
But the 'Impact_PPM' you see on the picture is not an actual column on the table, it's just a measure as a column in a table visual.

Re: How to add a measure's result of many lines

v-yueyunzh-msft — Thu, 10 Nov 2022 04:10:05 GMT

Hi, Anonymous

For your need , you can try to use this dax to create a measure :

PPM % = var _t = SUMMARIZE(ALL('Table'[Cod. Forn. Principal]) , 'Table'[Cod. Forn. Principal] , "PPM" , [Impacto_PPM] ) return DIVIDE([Impacto_PPM],SUMX(_t,[PPM]))

Then we can meet your need , the result is as follows:

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Re: How to add a measure's result of many lines

Anonymous — Thu, 10 Nov 2022 11:48:59 GMT

Hello v-yueyunzh-msft . Thank you for your time and for trying to help!

But just like my reply to our other friend, it didn't work...the results are the same.

Re: How to add a measure's result of many lines

FreemanZ — Thu, 10 Nov 2022 13:18:54 GMT

Can you simplify your report and upload the pbix file?

Re: How to add a measure's result of many lines

v-yueyunzh-msft — Fri, 11 Nov 2022 01:14:50 GMT

Hi , Anonymous

Can you check the measure type is "Percentage" in "Measure Tools"?

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Re: How to add a measure's result of many lines

carlosbpiolla — Fri, 11 Nov 2022 11:41:22 GMT

Hey, FreemanZ !! This is my personal account.

The situation is: the measure 'Porcentual PPM' is supposed to calculate the percentage that each 'Impact PPM' (which is also a measure) has on the total of the 'Impact PPM'. But theres is something wrong and I cant solve it.

Here's the pbix file with a few numbers on it.
samples.pbix
Thanks again for helping.

Re: How to add a measure's result of many lines

carlosbpiolla — Fri, 11 Nov 2022 11:58:28 GMT

Hey, v-yueyunzh-msft !! This is my personal account.

What I need is that the column %PPM looks like this.
Remember that both 'Impacto PPM' and '% PPM' are measures and not columns in the original table.

Cod. Forn. Principal	Entradas	Refugos	Impacto PPM	% PPM
16032835	8.839,00	1.200,00	834,91	82,47
15686750	16.300,00	94,00	65,4	6,46
15699333	20.584,00	76,00	52,88	5,22
15695126	39.720,00	60,00	41,75	4,12
15312500	7.600,00	20,00	13,92	1,38
15956300	92.589,00	4,00	2,78	0,27
11223000	3.190,00	1,00	0,7	0,069
and many more....	......	.....	.....	.....
TOTAL:	1.437.278	1455	1012,34	100%

Here is the pbix file with this data.

samples.pbix

Thanks again for helping!

Re: How to add a measure's result of many lines

zerotyper — Fri, 11 Nov 2022 13:20:08 GMT

carlosbpiolla I hope this is the result you want.

Re: How to add a measure's result of many lines

FreemanZ — Fri, 11 Nov 2022 13:55:18 GMT

It seems you put it on OneDrive, but i couldn't open it.

Re: How to add a measure's result of many lines

Anonymous — Fri, 11 Nov 2022 13:59:00 GMT

Thank you zerotyper !!! That's what I wanted!