cancel
Showing results for 
Search instead for 
Did you mean: 

Measure Totals, The Final Word

Super User IV
29276 Views
AlejandroPCar Helper IV
Helper IV

Re: Measure Totals, The Final Word

AMAZING! thanks to you a lot!

tkrupka Resolver II
Resolver II

Re: Measure Totals, The Final Word

I'm still trying to get this, and I just cannot wrap my head around it.

 

Fix Total.JPGCan't find the correct formula to replace "2"

tkrupka Resolver II
Resolver II

Re: Measure Totals, The Final Word

Nevermind, 

 

This worked:

 

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])))
samiller71
Regular Visitor

Re: Measure Totals, The Final Word

How can one apply this solution to a measure that references two additional measures, as shown below?

 

Lost Customers = IF([Total Orders]>0 && [Last Period Orders]<=0 ,1,0)
 
For your reference, below are the details of the two additional measures:
Total Orders = SUMX(RELATEDTABLE('Mfg Invoice Data-Mar'),'Mfg Invoice Data-Mar'[Quantity])
 
Last Period Orders = CALCULATE(
SUM('Mfg Invoice Data-Mar'[Quantity]),
DATESBETWEEN(
'Mfg Invoice Data-Mar'[Creation Date].[Date],
DATEADD(LASTDATE('Mfg Invoice Data-Mar'[Creation Date].[Date]),-1*[Selected Period],DAY),
LASTDATE('Mfg Invoice Data-Mar'[Creation Date].[Date])
)
)
 
 
I am trying to show a total of Lost Customers but the total is outputting "1".
mauroanelli Helper I
Helper I

Re: Measure Totals, The Final Word

very helpfull, but there's something missing for me because doesn't work.

 

i have this measure

Tot Costo/h = CALCULATE(SUMX(hr_analytic_timesheet;hr_analytic_timesheet[Ore])*MAX('Dipendenti Costo/H'[Costo H]))
 
try with your solution
 
Tot Costo/h_New =
VAR _table = SUMMARIZE(res_partner;[Partner];"_Value";[Tot Costo/h])
RETURN
IF(HASONEVALUE(res_partner[Partner]);[Tot Costo/h];SUMX(_table;[_Value]))
 
all the table are related
maulopez Advocate I
Advocate I

Re: Measure Totals, The Final Word

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! : )

OvidiuNeacsu Helper III
Helper III

Re: Measure Totals, The Final Word

Hello everyone,

 

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: 

PBIDesktop_HitMfMLmGk.png

 

The formula I use for the column in the middle is: 

 

From Interview Scheduled to Offer = IF ( ISBLANK([_Average days to Offer]) || ISBLANK([_Average days to Interview - Scheduled]), BLANK(),([_Average days to Offer] - [_Average days to Interview - Scheduled]))
 
The purpose of it is to ignore the calculation if the other column is blank and if that applies to give a blank result. Which works perfectly, except for the TOTALS. Is there something I can add to my formula so that the total is blank also unless the same candidate has both values? 
 
The 1st and 3rd column are also calculations that apply to columns that contain whole numbers (number of days from application to Interview or Offer in this case) or null values, that apply to each candidate (as I said one row = 1 candidate) 
 
_Average days to Interview - Scheduled = CALCULATE( AVERAGE([_Days Application to Interview - Scheduled]), FILTER(report, report[_Days Application to Interview - Scheduled] <> 0 ))
 
_Average days to Offer = CALCULATE( AVERAGE([_Days Application to Offer]), FILTER(report, report[_Days Application to Offer] <> 0 ))
 
I might be missing something here, can you please help me implement the "HASONEVALUE" function? Maybe it would be a solution here. 
 
Thank you very much!
calerof Responsive Resident
Responsive Resident

Re: Measure Totals, The Final Word

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?

 

Thanks,

 

Fernando

 

codjhs
Frequent Visitor

Re: Measure Totals, The Final Word

Do the formula work between two tables?

Thanks for your help

Highlighted
Sparta100
Frequent Visitor

Re: Measure Totals, The Final Word

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.