Forum Discussion

danielpaduck's avatar
danielpaduck
Helper III
2 years ago
Solved

Conditional Measure Is Not Totaling Correctly

Hi,

 

I have the following report for test:

 

 

I have regular hours, then forecasted hours (regular x 2) and then a conditional measure that states the following:  

 

Hours (Conditional) = IF([Period (msr)] >= 201901 && [Period (msr)] <= 201901, 'LD Actual'[Hours (Actual)], 'LD Forecast'[Hours (Forecast)])
 
Why is the total for this column not correct?  Each period itself is ok but the total is not. 
 
Thanks!

 

 

Need Help
  • bestevezdyb's avatar
    bestevezdyb
    2 years ago

    you can try, assuming you have the values in different tables. But it is as TomMartens  says, without the data model we cannot give an accurate answer.

     

    SUMX(
        FILTER(
            TableHour,
            TableHour[date] >= 201901 && TableHour[date] <= 201901
        ),
        TableHour[hour]
    )
    +
    SUMX(
        FILTER(
            TableHourF,
            TableHourF[date] > 201901
        ),
        TableHourF[hour]
    )

6 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    2 years ago

    Hey danielpaduck ,

     

    it's tough to explain the behavior of the measure because you do not provide any information about the underlying data model and also do not provide the statement for the measure [Period (msr)], as this measure is controlling the condition.

    Without explaining the business scenario the condition looks a little weird.

    Then the Total, if the measure [Period (msr)]  is somehow dependent on the column used on rows, namely Period, then you have to be aware that this column does not contribute to the filter context for the total line.

     

    Hopefully, this provides some ideas of how to check the result for the Total line.

     

    Regards,

    Tom

  • bestevezdyb's avatar
    bestevezdyb
    Frequent Visitor
    2 years ago

    you can try, assuming you have the values in different tables. But it is as TomMartens  says, without the data model we cannot give an accurate answer.

     

    SUMX(
        FILTER(
            TableHour,
            TableHour[date] >= 201901 && TableHour[date] <= 201901
        ),
        TableHour[hour]
    )
    +
    SUMX(
        FILTER(
            TableHourF,
            TableHourF[date] > 201901
        ),
        TableHourF[hour]
    )
  • danielpaduck's avatar
    danielpaduck
    Helper III
    2 years ago

    Hi,

     

    The following is my data model:  

     

    Essentially, what I am trying to accomplish is to show posted labor hours until a certain period and then after that period show the budget/forecasted hours.   There are three tables - CFGDates which contains the Period, LD Actual and LD Forecast.    It seems on the total, Power BI  does not have the Period filter anymore and then defaults to Actual hours.  

      • danielpaduck's avatar
        danielpaduck
        Helper III
        2 years ago

        I will try today but the periods will need to be more dyamic instead of hard coding period ranges. Thanks!

  • danielpaduck's avatar
    danielpaduck
    Helper III
    2 years ago

    How would it be possible to use variables insteaded of the hard coded values for the 201901?

     

    SUMX(
        FILTER(
            TableHour,
            TableHour[date] >= 201901 && TableHour[date] <= 201901
        ),
        TableHour[hour]
    )
    +
    SUMX(
        FILTER(
            TableHourF,
            TableHourF[date] > 201901
        ),
        TableHourF[hour]
    )
     
    Thanks!