SOLVED

Help with DAX formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2271786%22%20slang%3D%22en-US%22%3EHelp%20with%20DAX%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2271786%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20everyone%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20some%20help%20regarding%20DAX.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20in%20my%20example%2C%20I%20have%205%20Arenas%20(%20i.e.%2C%20products)%20where%20I%20am%20trying%20to%20create%20a%20DAX-formula%20showing%20total%20%22OO%20GM%25%22%20(Gross%20Margin%25).%20In%20order%20to%20do%20so%2C%20I%20have%20created%20a%20measure%20called%20%22Weighted%20%25%22%20which%20I%20want%20to%20multiply%20with%20each%20products%20%22Revshare_M%22.%20I%20get%20it%20correct%20for%20the%20individual%20arenas%20but%20the%20total%20is%20incorrect.%20I%20know%20that%20SUMX%20iterates%20row%20by%20row%2C%20but%20in%20my%20example%20one%20of%20the%20inputs%20is%20a%20measure%20and%20I%20don't%20know%20how%20to%20solve%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20really%20appreciate%20if%20someone%20could%20help%20me.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBr%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERobin%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2271786%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2272014%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20DAX%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2272014%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F798894%22%20target%3D%22_blank%22%3E%40Robin_Lindstrom%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20use%20measure%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3EOO%20GM%2C%20%25%3A%3DIF%20(%0A%20%20%20%20%20%20ISFILTERED(%20Arena%5BArena%5D)%2C%0A%20%20%20%20%20%20%5BOO%20GM%25%5D%2C%0A%20%20%20%20%20%20SUMX%20(%0A%20%20%20%20%20%20%20%20%20GROUPBY(Arena%2CArena%5BArena%5D%2CArena%5BArenaCode%5D)%2C%0A%20%20%20%20%20%20%5BOO%20GM%25%5D)%0A%20)%0A%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20347px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F272741i4C903CB5BC4E3DD6%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
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!