Forum Discussion

plantew1's avatar
plantew1
Helper I
2 years ago
Solved

Different Calculation at Category Level?

I have a measure that checks to see if there has been a greater than 5% change between two values and then counts the ones that have a 5% change. 
At the category level, I want to total up the ones that have a "1" in the values field to give a total.

However, the way that I've written my measure, it totals up the two compared values and does the same calculation at the Category level as the Indicator level and doesn't show the total in Category level when the 5% change isn't found in the Category level. I'm wondering how to rewrite the measure so that it totals up the indicator values (the "1"s ) but does not apply the same "check of the 5%" at the Category level. I have tried using "IsFiltered" without success.

Any clues would be greatly appreciated.

Measure =

VAR _RecentDate = MAX( 'CIHI___LTC_Data_Quality'[Begin Date] )
VAR _SecondRecentDate = CALCULATE( MAX( 'CIHI___LTC_Data_Quality'[Begin Date] ), 'CIHI___LTC_Data_Quality'[Begin Date] < _RecentDate )

VAR _Calc1 =
CALCULATE(
    SUM('CIHI___LTC_Data_Quality'[Indicator Value] ),
    'CIHI___LTC_Data_Quality'[Begin Date] = _RecentDate
)


VAR _Calc2 =
CALCULATE(
    SUM( 'CIHI___LTC_Data_Quality'[Indicator Value] ),
    'CIHI___LTC_Data_Quality'[Begin Date] = _SecondRecentDate
)


VAR _PercentofChange =
    ABS( DIVIDE( (_Calc1 - _Calc2), _Calc2) )
   
VAR _YESNO =
    IF(
        _PercentofChange <= .05,
        BLANK(),
        1
    )




RETURN
IF(
    ISFILTERED( 'LTC Indicators'[Indicator] ),
    SUMX(
        VALUES( 'LTC Indicators'[Indicator] ),
        _YESNO
    ),
    IF(
        ISFILTERED( 'LTC Indicators'[Indicator Category] ),
        SUMX(
            VALUES( 'LTC Indicators'[Indicator Category] ),
            _YESNO
        )
    )
)

 

Need Help
  • Ritaf1983's avatar
    Ritaf1983
    2 years ago

    Hi plantew1 
    Try to use isinscope instead of isfiltered.

    https://www.youtube.com/watch?v=DtOfcsS_pQw

    if it doesn't help 

    Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
    https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
    Please show the expected outcome based on the sample data you provided.

    https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

     

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi plantew1 

    You can consider to create two measures

     

    Measure1 =
VAR _RecentDate =
    MAX ( 'CIHI___LTC_Data_Quality'[Begin Date] )
VAR _SecondRecentDate =
    CALCULATE (
        MAX ( 'CIHI___LTC_Data_Quality'[Begin Date] ),
        'CIHI___LTC_Data_Quality'[Begin Date] < _RecentDate
    )
VAR _Calc1 =
    CALCULATE (
        SUM ( 'CIHI___LTC_Data_Quality'[Indicator Value] ),
        'CIHI___LTC_Data_Quality'[Begin Date] = _RecentDate
    )
VAR _Calc2 =
    CALCULATE (
        SUM ( 'CIHI___LTC_Data_Quality'[Indicator Value] ),
        'CIHI___LTC_Data_Quality'[Begin Date] = _SecondRecentDate
    )
VAR _PercentofChange =
    ABS ( DIVIDE ( ( _Calc1 - _Calc2 ), _Calc2 ) )
RETURN
    IF ( _PercentofChange <= .05, BLANK (), 1 )

    Measure2 =
IF (
    ISINSCOPE ( 'LTC Indicators'[Indicator Category] ),
    SUMX ( VALUES ( 'LTC Indicators'[Indicator Category] ), [Measure1] ),
    IF (
        ISINSCOPE ( 'LTC Indicators'[Indicator] ),
        SUMX ( VALUES ( 'LTC Indicators'[Indicator] ), [Measure1] )
    )
)

     

    Then put the  Measure2 to the matrix.

     

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

4 Replies

  • Ritaf1983's avatar
    Ritaf1983
    Super User
    2 years ago

    Hi plantew1 
    Try to use isinscope instead of isfiltered.

    https://www.youtube.com/watch?v=DtOfcsS_pQw

    if it doesn't help 

    Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
    https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
    Please show the expected outcome based on the sample data you provided.

    https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

     

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

    • plantew1's avatar
      plantew1
      Helper I
      2 years ago

      Thanks for pointing me in the right direction. Greatly appreciated.

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi plantew1 

    You can consider to create two measures

     

    Measure1 =
VAR _RecentDate =
    MAX ( 'CIHI___LTC_Data_Quality'[Begin Date] )
VAR _SecondRecentDate =
    CALCULATE (
        MAX ( 'CIHI___LTC_Data_Quality'[Begin Date] ),
        'CIHI___LTC_Data_Quality'[Begin Date] < _RecentDate
    )
VAR _Calc1 =
    CALCULATE (
        SUM ( 'CIHI___LTC_Data_Quality'[Indicator Value] ),
        'CIHI___LTC_Data_Quality'[Begin Date] = _RecentDate
    )
VAR _Calc2 =
    CALCULATE (
        SUM ( 'CIHI___LTC_Data_Quality'[Indicator Value] ),
        'CIHI___LTC_Data_Quality'[Begin Date] = _SecondRecentDate
    )
VAR _PercentofChange =
    ABS ( DIVIDE ( ( _Calc1 - _Calc2 ), _Calc2 ) )
RETURN
    IF ( _PercentofChange <= .05, BLANK (), 1 )

    Measure2 =
IF (
    ISINSCOPE ( 'LTC Indicators'[Indicator Category] ),
    SUMX ( VALUES ( 'LTC Indicators'[Indicator Category] ), [Measure1] ),
    IF (
        ISINSCOPE ( 'LTC Indicators'[Indicator] ),
        SUMX ( VALUES ( 'LTC Indicators'[Indicator] ), [Measure1] )
    )
)

     

    Then put the  Measure2 to the matrix.

     

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.