SOLVED

Help with DAX formula

Contributor

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

@Robin_Lindstrom 

You may use measure like

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

image.png

@Robin_Lindstrom 

Or you may modify the measure like this...

 

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

 

@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

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

@Subodh_Tiwari_sktneer 

 

Absolutely!

Please see attached

Kind Regards,

 

Robin

@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.

best response confirmed by Robin_Lindstrom (Contributor)
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, %])

image.png

 

@Sergei Baklan 

 

Brilliant!

Thank you so much for your help

Kind Regards,

 

Robin

@Subodh_Tiwari_sktneer 

 

Awesome!

Thank you for your time and help

Kind Regards,

 

Robin

@Robin_Lindstrom , you are welcome

You're welcome @Robin_Lindstrom!