Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Summarize a variable's result in a measure

I have a measure which is returning a percentage from 0-100% and I have a matrix table with 4 level (1 - highest, 4 - transactional level).

What I would like to achieve is that based on my measure I would like to have another measure which is returning "1" on the level 4th (transactional) and have a second calculation which will summarize the 4th level's "1" on the 3rd level

Elementary_KPI = the KPI which result is from 0% to 100%

 

--------------------------------------------------------------------------------

 

BASIC_KPI =

VAR CALC_KPI = IF([Elementary_KPI] <> 0,1)
VAR SUM_CALC_KPI = SUMX(IF([Elementary_KPI] <> 0,1))

VAR FINAL =
  IF (ISINSCOPE('TABLE'[4th_level]),CALC_KPI
, IF (ISINSCOPE('TABLE'[3th_level]),SUM_CALC_KPI))
RETURN FINAL

So the scope is the to summarize CALC_KPI's result in the SUM_CALC_KPI, is it possible or did I approach this from a wrong direction? 
Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    Hi, Anonymous 

    Please try formula as below:

    CALC_KPI = IF([Elementary_KPI] <> 0,1)+0 
    SUM_CALC_KPI = SUMX(VALUES('Table'),[CALC_KPI])
    BASIC_KPI = 
IF (
    ISINSCOPE ( 'Table'[4th_level] ),
    [SUM_CALC_KPI],
    IF ( ISINSCOPE ( 'Table'[3th_level] ), [SUM_CALC_KPI], 'Table'[Elementary_KPI] )
)

    result:

    Best Regards,
    Community Support Team _ Eason

2 Replies

  • v-easonf-msft's avatar
    v-easonf-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, Anonymous 

    Please try formula as below:

    CALC_KPI = IF([Elementary_KPI] <> 0,1)+0 
    SUM_CALC_KPI = SUMX(VALUES('Table'),[CALC_KPI])
    BASIC_KPI = 
IF (
    ISINSCOPE ( 'Table'[4th_level] ),
    [SUM_CALC_KPI],
    IF ( ISINSCOPE ( 'Table'[3th_level] ), [SUM_CALC_KPI], 'Table'[Elementary_KPI] )
)

    result:

    Best Regards,
    Community Support Team _ Eason

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi v-easonf-msft, 

      Thank you so much for the help! One thing, you wrote the following code: 

       

        IF ( ISINSCOPE ( 'Table'[3th_level] ), [SUM_CALC_KPI], 'Table'[Elementary_KPI] )
)

       

      I have to modify to that: 

       

        IF ( ISINSCOPE ( 'Table'[3th_level] ), [SUM_CALC_KPI], [Elementary_KPI] )
)

       

      With this it is working now.