Apr 14 2021 12:32 AM
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
Apr 14 2021 02:37 AM
You may use measure like
OO GM, %:=IF (
ISFILTERED( Arena[Arena]),
[OO GM%],
SUMX (
GROUPBY(Arena,Arena[Arena],Arena[ArenaCode]),
[OO GM%])
)
Apr 14 2021 03:28 AM
Or you may modify the measure like this...
OO GM% :=
IF (
ISFILTERED ( Arena[Arena] ),
[Revshare_M] * [Weighted %],
SUMX (
Arena,
[Revshare_M] * [Weighted %]
)
)
Apr 14 2021 03:38 AM
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
Apr 14 2021 04:30 AM
Apr 14 2021 05:05 AM
Apr 14 2021 08:43 AM - edited Apr 14 2021 08:49 AM
Apr 14 2021 08:43 AM - edited Apr 14 2021 08:49 AM
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.
Apr 14 2021 09:48 AM
SolutionAs 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, %])
Apr 14 2021 11:59 PM
Apr 15 2021 12:00 AM
Apr 14 2021 09:48 AM
SolutionAs 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, %])