Measure with an if statement

Question

Hello,&nbsp;&nbsp;First time posting in the forum, apologies if this has been answered, I'm not even quite sure what I'm doing wrong to be able to look up my problem.&nbsp;&nbsp;&nbsp;I have two fact tables - Planned and Actuals, each containing a field called TOTAL PLANNED TONNAGE and TOTAL Actual TONNAGE respectively. I would like to compare the two to see what was done "In Plan" by Workplace and Month for a variety of different plans.&nbsp;&nbsp;&nbsp;&nbsp;My DAX code for the InPlan measure is below. It functions correctly at the Workplace Row level, but when I look at the month, it's using the totals for the month to do the comparison, and I somehow need it to still compare it based on Workplace. So for the shown table, I'm expecting the result to be 6,220 for January (sum of 1150, 70, 5000), not 9,500.&nbsp;&nbsp;&nbsp;I believe what I need to do is somehow force the filter by WORKPLACE somehow, but I just can't wrap my head around this yet.&nbsp;&nbsp;Report Table:&nbsp;&nbsp;Relationships&nbsp;DAX code for InPlan:&nbsp;&nbsp;InPlan&nbsp;=IF&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;OR&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SUM&nbsp;(&nbsp;Plans[TOTAL&nbsp;PLANNED&nbsp;TONNAGE]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ALLEXCEPT&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Plans,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Calendar'[Month&nbsp;Name],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Dim_Workplaces[WORKPLACE],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Plans[Plan#]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;=&nbsp;0,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SUM&nbsp;(&nbsp;Actuals[TOTAL&nbsp;Actual&nbsp;TONNAGE]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ALLEXCEPT&nbsp;(&nbsp;Actuals,&nbsp;'Calendar'[Month&nbsp;Name],&nbsp;Dim_Workplaces[WORKPLACE]&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;=&nbsp;0&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;0,&nbsp;&nbsp;&nbsp;&nbsp;IF&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SUM&nbsp;(&nbsp;Plans[TOTAL&nbsp;PLANNED&nbsp;TONNAGE]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ALLEXCEPT&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Plans,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Calendar'[Month&nbsp;Name],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Dim_Workplaces[WORKPLACE],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Plans[Plan#]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&gt;=&nbsp;CALCULATE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SUM&nbsp;(&nbsp;Actuals[TOTAL&nbsp;Actual&nbsp;TONNAGE]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ALLEXCEPT&nbsp;(&nbsp;Actuals,&nbsp;'Calendar'[Month&nbsp;Name],&nbsp;Dim_Workplaces[WORKPLACE]&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SUM&nbsp;(&nbsp;Actuals[TOTAL&nbsp;Actual&nbsp;TONNAGE]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ALLEXCEPT&nbsp;(&nbsp;Actuals,&nbsp;'Calendar'[Month&nbsp;Name],&nbsp;Dim_Workplaces[WORKPLACE]&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SUM&nbsp;(&nbsp;Plans[TOTAL&nbsp;PLANNED&nbsp;TONNAGE]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ALLEXCEPT&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Plans,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Calendar'[Month&nbsp;Name],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Dim_Workplaces[WORKPLACE],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Plans[Plan#]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;))&nbsp;A simplified view of the calculation is this:&nbsp;&nbsp;if (or (Plan Tonnage = 0, Actual Tonnage = 0), 0 , if (Plan Tonnage &gt;= Actual Tonnage, Actual Tonnage, Plan Tonnage)&nbsp;&nbsp;&nbsp; Any help is greatly appreciated!&nbsp;&nbsp;&nbsp;

datainsights · Accepted Answer

AUEng_403,
&nbsp;
Try these measures. CROSSJOIN creates every combination of Date and Workplace in the filter context. Then, ADDCOLUMNS calculates the Plan Tonnage and Actual Tonnage for each combination of Date and Workplace, and SUMX sums them. This forces the total to equal the sum of its parts.
&nbsp;
Plan Tonnage = SUM (Plans[TOTAL PLANNED TONNAGE] )Actual Tonnage = SUM ( Actuals[TOTAL Actual TONNAGE] )InPlan = 
VAR vBaseTable =
    CROSSJOIN ( VALUES ( Calendar[Date] ), VALUES ( Dim_Workplaces[WORKPLACE] ) )
VAR vCalcTable =
    ADDCOLUMNS ( vBaseTable, "@Plan", [Plan Tonnage], "@Actual", [Actual Tonnage] )
VAR vResult =
    SUMX (
        vCalcTable,
        SWITCH (
            TRUE,
            [@Plan] = 0 || [@Actual] = 0, 0,
            [@Plan] &gt;= [@Actual], [@Actual],
            [@Plan]
        )
    )
RETURN
    vResult
&nbsp;

&nbsp;

Forum Discussion

Measure with an if statement

1 Reply

Related content

PowerBI measure IF statement

Measure IF statement for each row

If Statement in Measure

DAX Measure with Nested IF Statements

Help with optimizing DAX measure - Income statement