topic Re: How to calculate % in a row in DAX Commands and Tips

How to calculate % in a row

vilden — Tue, 29 Nov 2022 12:45:53 GMT

Hello, i have this formula - how to change it to count ratio in all rows, so i need to have 100% in all rows, now i have 100% in total. SUMX in all rows should be 1. Thanks.

Tried KEEPFILTERS, but then error occured

Re: How to calculate % in a row

v-jialluo-msft — Wed, 30 Nov 2022 09:35:38 GMT

^{Hi vilden ,}

^{You can try:}

SUMX = DIVIDE ( SUM ( 'KP'[COUNT_DISTINCT_of_K_PODPROD] ), CALCULATE ( SUM ( 'KP'[COUNT_DISTINCT_of_K_PODPROD] ), ALL ( 'KP' ) ) )

^{If that's not what you need, upload a sample file to resolve the issue faster.}

^{Best Regards,}

^{Gallen Luo}

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

Re: How to calculate % in a row

pi_eye — Wed, 30 Nov 2022 10:01:48 GMT

Hi vilden

Is this the kind of thing that you are after?

If so, the filter expression you need is allselected (column). For the above example, the expression is

percent = divide([SumOfValue], CALCULATE([SumOfValue],allselected(WareHouse[Year])))

Year is the column header, so calculate will ignore that for the total.

SumOfValue is a sum() expression similar to what you have in your example.

If not - please can you illustrate what you are looking for as this will help solve the isssue.

HTH,

Re: How to calculate % in a row

vilden — Thu, 01 Dec 2022 07:19:54 GMT

YES, i need exactly what u described, to count % in a rows, but i tried ur formula and it didnt change the result, why 😞 ?

Re: How to calculate % in a row

vilden — Thu, 01 Dec 2022 07:24:26 GMT

Thanks for try 🙂 but i need to have it compared in a row, so in all row i need to count it to 100% so for example in first row i need to have 367/414 in percent and 47/414 in percent so sum is 100% in all rows. How can i do it pls?:

Re: How to calculate % in a row

vilden — Thu, 01 Dec 2022 07:28:52 GMT

Tried this and have 100% in every value, why?

Re: How to calculate % in a row

v-jialluo-msft — Thu, 01 Dec 2022 08:10:36 GMT

^{Hi vilden ,}

^{This issue does not seem complicated, maybe you need to filter the data once.}

^{If you can upload sample data, it can be better solved.}

^{You can check out similar cases first:}

Solved: Re: Donut Chart Cross Filtering- Showing percentag... - Microsoft Power BI Community

^{Best Regards,}

^{Gallen Luo}

Re: How to calculate % in a row

vilden — Thu, 01 Dec 2022 08:19:20 GMT

Here is sample of data what i used in matrix table:

Re: How to calculate % in a row

pi_eye — Thu, 01 Dec 2022 19:47:48 GMT

Try it with the other dimension!

I can see that the total on the bottom for SUMX is 1, and not the total on the right.

Instead of Allselected(Partner)

Replace with AllSelected(Prevodnik)

Re: How to calculate % in a row

pi_eye — Thu, 01 Dec 2022 19:49:28 GMT

For reference - if you see in my example

Year - is the column headings

percent = divide([SumOfValue], CALCULATE([SumOfValue],allselected(WareHouse[Year])))

You have put the row headings - so just needsd swapping

Re: How to calculate % in a row

vilden — Fri, 02 Dec 2022 08:05:45 GMT

Thanks, that solved it. This is awesome community!