topic Calculate dynamic Weighted Average of a Measure vs the Total in DAX Commands and Tips

Calculate dynamic Weighted Average of a Measure vs the Total

mciani — Thu, 06 May 2021 08:18:42 GMT

Hi community,

I have read tons of posts but I cannot find a solution to my issue.

I'm trying to calculate the % of a row value, resulting from a measure in a Matrix, vs the column total.

In excel, it will looks like the following:

Channel	Trade Spend	WA
Coop e Gruppi T.O.	1.030.061	14,99%
Corner MM	513.728	7,48%
Dirette	1.977.293	28,78%
E-commerce	485.555	7,07%
Ingrosso	927.850	13,51%
Intercompany	0	0,00%
Mass Market	1.644.977	23,95%
Others	138	0,00%
Total	6.869.443	*100,00%*

where WA is the result of the following formula:

=B2/$B$10.

The trade spend column is a measure that sums other measures.

is there a way to replicate the same output in PBI?

Thank you all for your suggestion!

Marco

Re: Calculate dynamic Weighted Average of a Measure vs the Total

Jihwan_Kim — Thu, 06 May 2021 08:29:27 GMT

Hi, mciani

Please check the below picture and the sample pbix file's link down below.

WA Percentage =

DIVIDE (

SUM ( Data[Trade Spend] ),

CALCULATE ( SUM ( Data[Trade Spend] ), ALL ( Data ) )

)

https://www.dropbox.com/s/83e3vso3agd6jb6/mciani.pbix?dl=0

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

Re: Calculate dynamic Weighted Average of a Measure vs the Total

mciani — Thu, 06 May 2021 11:44:53 GMT

Hi Jihwan,

I see you created a data table and build a calculation in dax that is using the SUM function.

I cannot use SUM as the value I need to reference to is a Measure. Is there any other way to do the same with the measure?

I can provide the pbix if it's easier

Re: Calculate dynamic Weighted Average of a Measure vs the Total

Jihwan_Kim — Thu, 06 May 2021 11:46:35 GMT

Hi, mciani

Thank you for your feedback.

SUM ( Data[Trade Spend] ) is a measure and you can try to replace this to your measure.

I am not sure how your measure is related to the column, so that is why I created a sample by myself.

Please share your sample pbix file, then I can try to come up with a more accurate measure.

Thank you.

Re: Calculate dynamic Weighted Average of a Measure vs the Total

mciani — Thu, 06 May 2021 15:08:35 GMT

Thanks for your support Jihwan_Kim .

Here is the link to the pbix file.

Thanks again

Re: Calculate dynamic Weighted Average of a Measure vs the Total

Jihwan_Kim — Thu, 06 May 2021 12:07:15 GMT

Hi, mciani

Thank you for sharing.

Please check the below picture and the pbix file's link down below whether it is what you are looking for.

WA percentage =

DIVIDE (

[TRADE SPEND],

CALCULATE ( [TRADE SPEND], ALLSELECTED ( PowerBI_Customer[Channel] ) )

)

https://www.dropbox.com/s/n2rqexmgolitgrf/Gross%202%20Net.pbix?dl=0

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

Re: Calculate dynamic Weighted Average of a Measure vs the Total

mciani — Thu, 06 May 2021 12:16:35 GMT

Hi Jihwan_Kim ,

that is exactely what I was looking for! THANK YOU!

One last thing. In the formula you used "ALLSELECTED ( PowerBI_Customer[Channel] ) )"

This will aways return the value compared with Channel. Is there a way to make this dynamic to the first row in the matrix? For instance, if I want to use the month to split rows instead of the channel? Or do I have to create as many new measue with all the possible combination?

Re: Calculate dynamic Weighted Average of a Measure vs the Total

Jihwan_Kim — Thu, 06 May 2021 12:47:31 GMT

Hi, mciani

Thank you for your feedback.

You can also try the below.

WA percentage V2 =

DIVIDE (

[TRADE SPEND],

CALCULATE ( [TRADE SPEND], ALLSELECTED ( PowerBI_Total_PL) )

)

Then, you can change the Channel column to the Month column, for instance.

However, I am not quite sure if it gives the correct answer for all situations. Because the measures that are related to [TRADE SPEND] have quite a lot of conditions. It is really hard to tell whether it will still show the correct result if ALLSELECTED is used with the main fact table.

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

Re: Calculate dynamic Weighted Average of a Measure vs the Total

mciani — Thu, 06 May 2021 14:59:50 GMT

THANK YOU!!!