How to dynamically calculate the percentage for more than one parameter (column)

[bmms]

Hello,

i have a chart where i can show one of two fields (country or product) in Y axie and show the respective percentage in X axie.

I created a parameter based in two fields (country or product), then i choose wich field i want to appear in Y axie (using one sclicer).

Finnaly, i have other slicer by year.

But, i don't know how to dynamically calculate the percentage because the Y axies can be changed by user and i only can assigned one measure on X axie!

Can you help me?

I have the follow table:

The follow parameter:

Charts (but with wrong values, it is expected to use percentage)

In addiction, i shared a link for download an example:

https://we.tl/t-X16hFHWDJ5

best regards!

[Anonymous · Accepted solution]

Hi @bmms 

I cannot download your example, so I create a sample, and you can try the following measure:

Measure = SWITCH(TRUE(),SELECTEDVALUE(Parameter[Parameter Order])=1,DIVIDE(SUMX(FILTER(ALL(Sales),[Country]=SELECTEDVALUE(Sales[Country])),[Value]),SUMX(ALL(Sales),[Value])),SELECTEDVALUE(Parameter[Parameter Order])=0,DIVIDE(SUMX(FILTER(ALL(Sales),[Product]=SELECTEDVALUE(Sales[Product])),[Value]),SUMX(ALL(Sales),[Value])),BLANK())

Since I don't know exactly what the percentage on your side is looking for, I calculate the percentage of total value for each country/product

 Output:

 ^{Best Regards,}

^{Yolo Zhu}

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

[bmms]

Hello,

thanks for your help.

I used your measure, but the percentage it is wrong.

For example, If I Select year = 2021 and product = "C", the correct percentage is 15,09%, but using your measure the percentage is 15,29%.

However, I made some changes to the measure, and i got the correct percentage. I just changed "All" for "AllSelected"!!

Percentage = SWITCH(

TRUE(),

    SELECTEDVALUE('Product/Country'[Product/Country Order]) = 1, DIVIDE(SUMX(FILTER(ALLSELECTED(Sales), [Country] = SELECTEDVALUE(Sales[Country])),[Value]), SUMX(ALLSELECTED(Sales),[Value])),

    SELECTEDVALUE('Product/Country'[Product/Country Order]) = 0, DIVIDE(SUMX(FILTER(ALLSELECTED(Sales), [Product] = SELECTEDVALUE(Sales[Product])),[Value]), SUMX(ALLSELECTED(Sales),[Value]))

,BLANK()

)

Please see the follow pictures

Wrong:

Correct:

Why Is the percentage wrong when i use "All" in measure"?

Best Regards

[Anonymous]

Hi @bmms 

My measure is based on the parameters you gave without taking into account the filter year, and the all() function filters out all filters and is therefore unaffected by the filter

Best Regards!

Yolo Zhu