topic Hot to get Sum of Avg in Matrix in DAX Commands and Tips

Hot to get Sum of Avg in Matrix

Happy2023_05 — Sat, 07 Jan 2023 11:52:28 GMT

Avg1-1%

Avg2-2%

Avg3- 4%

In Matrix when I drag this average measure in total I get avg value-2.3% but I have to get sum=7%.pls help me to achieve this?

Re: Hot to get Sum of Avg in Matrix

tamerj1 — Sat, 07 Jan 2023 12:09:18 GMT

Hi Happy2023_05

please use

SUMX (

VALUES ( 'Table'[the slicing by column at rows of the matrix] ),

[Average Measure]

)

If you are slicing by multiple columns from different tables then

SUMX (

SUMMARIZE ( 'Fact Table', 'Dim Table1'[columnX], 'Dim Table2'[columnY], ... ),

[Average Measure]

)

Re: Hot to get Sum of Avg in Matrix

Happy2023_05 — Sat, 07 Jan 2023 17:07:13 GMT

tamerj1

Thank you so much it works.

But the summation is not correct for integer

Suppose - 7% +0% total showing -8% which is not correct.

But in decimal it shows correct

7.28%+0.33%=7.61%.

I want it as integer so value should be 7%. Round function not working for percentage.

Round(sumx(),avg measure),0).

Re: Hot to get Sum of Avg in Matrix

tamerj1 — Sat, 07 Jan 2023 19:06:10 GMT

Happy2023_05

Use

VALUES ( 'Table'[the slicing by column at rows of the matrix] ),

ROUND ( [Average Measure], 0 )

)

Re: Hot to get Sum of Avg in Matrix

Happy2023_05 — Sun, 08 Jan 2023 03:49:54 GMT

I tried it already round(avg measure) gives 0% individual value and sum becomes 0%.this does not work. Is there any other way to achieve it?

Re: Hot to get Sum of Avg in Matrix

tamerj1 — Sun, 08 Jan 2023 04:53:18 GMT

Happy2023_05

You wrapped the whole SUMX with ROUND. You need to wrap only the measure inside SUMX

SUMX (

VALUES ( 'Table'[the slicing by column at rows of the matrix] ),

ROUND ( [Average Measure], 0 )

)

Re: Hot to get Sum of Avg in Matrix

Happy2023_05 — Sun, 08 Jan 2023 12:04:32 GMT

I did the way you mentioned above.Wrapped round function inside sumx not whole sumx. That did not work. It rounds each avg measure value to 0%

Re: Hot to get Sum of Avg in Matrix

tamerj1 — Sun, 08 Jan 2023 13:49:17 GMT

Happy2023_05

Please provide a screenshot for better understanding of your problem

Re: Hot to get Sum of Avg in Matrix

Happy2023_05 — Sun, 08 Jan 2023 17:21:36 GMT

AVG = AVERAGE(Table1[sales%]) .

I could not see any option to attach PBIX file so sharing screenshot.