04-12-2019 20:43 PM
Trial = IF(ISFILTERED(GeneratorNames[GeneratorName])&&ISFILTERED(RunData[Date])&&ISFILTERED(Locations[Location])&&ISFILTERED(IBXs[IBX_Name]), SUMX(RunData,[Engine Run Time]*SUMX(GeneratorNames,GeneratorNames[FuelRate])), VAR __table = SUMMARIZE(RunData,Locations[Location],IBXs[IBX_Name],GeneratorNames[GeneratorName],RunData[Date],"__value",SUMX(RunData,[Engine Run Time]*SUMX(GeneratorNames,GeneratorNames[FuelRate]))) RETURN IF(ISFILTERED(GeneratorNames[GeneratorName])&&ISFILTERED(RunData[Date])&&ISFILTERED(Locations[Location])&&ISFILTERED(IBXs[IBX_Name]), SUMX(RunData,[Engine Run Time]*SUMX(GeneratorNames,GeneratorNames[FuelRate])),SUMX(__table,[__value])))
How can one apply this solution to a measure that references two additional measures, as shown below?
very helpfull, but there's something missing for me because doesn't work.
i have this measure
Amazing! I just tried this Greg's solution after days of struggling with meassure totals for the first time and it worked perfectly. It is indeed amaizing that Microsoft hasn't come out with a solution of their won for this problem alredy coded in PBI, but this solution is a real wonder. Thanks so much Greg, you're a genious ! BTW, the poem is fantastic too! : )
I am trying to calculate some average times and it works well, except the Totals. Each row represents a candidate.
The table I have looks like this:
The formula I use for the column in the middle is:
Hi @Greg_Deckler ,
I have a question regarding the use of a VAR in the summarization. In that case the result is wrong. I have to use a VAR due to a complex commission calculation based on different levels of revenue and % margin.
For instance, if revenue > $250,000 and margin % is less than 56% but greater than 52% the commision % is 1%,
with the same revenue limit if margin is less than 60% but greater than 56% the commission % is 1.5%, etc.
To handle this calculation I created VAR as follows:
Comision = VAR Sales= [Sales Total] VAR Margin = [Margin Total] VAR Commission = SWITCH( TRUE(), Sales >= 200000 && Sales < 250000, 0, Sales>= 250000 && Margin >= 0.52 && Margin < 0.56, 0.01, Sales>= 250000 && Margin < 0.60, 0.015, Sales>= 250000 && Margin >= 0.6, 0.02, 0 ) VAR BaseBonus = SWITCH( TRUE(), Sales>= 200000 && Sales < 250000, 1000, 0 ) VAR CommissionAmount = Sales * Commission + BaseBonus VAR ComisTotal = SUMMARIZE( VALUES( OCRD[SlpCode] ), OCRD[SlpCode], "ComisTot", MAX( 0, CommissionAmount ) ) RETURN SUMX( ComisTotal, [ComisTot] )
At using a VAR after RETURN it gives a wrong total.
I made a dummy data model to show this procedure here.
Could you please advice?
Thank you Greg. I was able to obtain the correct totals for a simple (yet critical) sum of absolute values calculation in Power Pivot by adapting your technique. What I thought would be a three second "wrap in ABS" formula turned into a three (12-hour) day nightmare. Cannot believe what I just went through to achieve what is literally the dead-simplest calculation mathematically possible. But can't thank you enough for this solution.