Help with DAX formula

[Robin_Lindstrom]

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

[#2]

@Robin_Lindstrom 

You may use measure like

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

[#3]

@Robin_Lindstrom 

Or you may modify the measure like this...

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

[#4]

@Subodh_Tiwari_sktneer 

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

[#5]

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

[#6]

@Subodh_Tiwari_sktneer 

Absolutely!

Please see attached :)

Kind Regards,

Robin

[#7]

@Robin_Lindstrom 

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.

[#8 · Accepted solution]

@Robin_Lindstrom 

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, %])

[#9]

@Sergei Baklan 

Brilliant!

Thank you so much for your help :)

Kind Regards,

Robin

[#10]

@Subodh_Tiwari_sktneer 

Awesome!

Thank you for your time and help :)

Kind Regards,

Robin

[#11]

@Robin_Lindstrom , you are welcome

[#12]

You're welcome @Robin_Lindstrom!