SOLVED

Contributor

# Help with DAX formula

Hello everyone,

I need some help regarding DAX.

So in my example, I have 5 Arenas ( i.e., products) where I am trying to create a DAX-formula showing total "OO GM%" (Gross Margin%). In order to do so, I have created a measure called "Weighted %" which I want to multiply with each products "Revshare_M". I get it correct for the individual arenas but the total is incorrect. I know that SUMX iterates row by row, but in my example one of the inputs is a measure and I don't know how to solve it.

I really appreciate if someone could help me.

Br,

Robin

11 Replies

# Re: Help with DAX formula

You may use measure like

``````OO GM, %:=IF (
ISFILTERED( Arena[Arena]),
[OO GM%],
SUMX (
GROUPBY(Arena,Arena[Arena],Arena[ArenaCode]),
[OO GM%])
)
``````

# Re: Help with DAX formula

Or you may modify the measure like this...

``````OO GM% :=
IF (
ISFILTERED ( Arena[Arena] ),
[Revshare_M] * [Weighted %],
SUMX (
Arena,
[Revshare_M] * [Weighted %]
)
)``````

# Re: Help with DAX formula

This actually works, but when displaying more than one package the "weighted" should pay attention to the individual package and should always add upp to 100%, otherwise it seems to work.

Any ideas?

br,

R

# Re: Help with DAX formula

Can you mock up the desired output as you did in your first sample file and upload it again?

Absolutely!

Kind Regards,

Robin

# Re: Help with DAX formula

Create the below measure first...

``````Brutto_M_All_Arena :=
CALCULATE (
CALCULATE (
SUMX (
Arena,
Arena[Kontakter 8.33%] * Arena[CPC]
),
ALL ( Arena[Arena] )
),
CROSSFILTER ( Arena[ArenaCode], Arena_Paket[ArenaCode], BOTH )
)``````

And then modify your weighted measure like below...

``Weighted %_2:=DIVIDE([Brutto_M],[Brutto_M_All_Arena])``

And see if that returns what you are trying to visualize.

best response confirmed by Robin_Lindstrom (Contributor)
Solution

# Re: Help with DAX formula

As variant

``````Weighted %%:=VAR
allBrutto=CALCULATE(
[Brutto_M],
ALLEXCEPT(Arena,Arena[ArenaCode])
)
RETURN DIVIDE([Brutto_M], allBrutto)

OO GM, %%:=IF (
ISFILTERED( Arena[Arena]),
[Weighted %%]*[Revshare_M],
[OO GM, %])``````

# Re: Help with DAX formula

Brilliant!

Thank you so much for your help

Kind Regards,

Robin

# Re: Help with DAX formula

Awesome!

Thank you for your time and help

Kind Regards,

Robin

# Re: Help with DAX formula

@Robin_Lindstrom , you are welcome

# Re: Help with DAX formula

You're welcome @Robin_Lindstrom!