Forum Discussion

harshadrokade's avatar
harshadrokade
Post Partisan
4 years ago
Solved

KPI indicator visual not working properly

Hi All,

 

I have data as below sample for multiple students & their years & subjects-

Student nameYearSubjectValue
A2018A115
A2018A239
A2018A341
A2018A441
A2018A547
A2019A125
A2019A230
A2019A332
A2019A433
A2019A523
A2021A149
A2021A238
A2021A314
A2021A419
A2021A524

 

I have created below measures to get the scoring of a specific subject (A1) for recent year & 2nd most recent year-

 

1) Recent value = calculate(AVERAGE(Sheet4[Value]),Sheet4[Year]=MAX( Sheet4[Year] ),Sheet4[Subject]="A1")
 
2) 2nd most recent yar value = calculate(sum(Sheet4[Value]),filter(Sheet4,Sheet4[Year]=calculate(max(Sheet4[Year]),filter(Sheet4,Sheet4[Year]< MAX(Sheet4[Year] )))),Sheet4[Subject]="A1")
 

I have created visuals on the screen as below-

 

1) Slicer for Student name

 

2) KPI indicator to get the recent value, difference between recent value & 2nd most recent value & trend of three years

 

 

The issue is the difference is being shown as infinity. Ideally the difference should come as below which I get when I remove Year value from 'Trend Axis' field. But when I add it to see the trend of three years, its giving incorrect difference as above. 

 

If we put the measures on card as below, we can see they are showing correct recent and 2nd most recent value of Subject A1 for selected student but when we add Year value in 'Trend Axis' its all leading to issue. 

 

Pls help on how to get the trend added in KPI card along with recent value & difference with 2nd most recent value shown on KPI indicator?

 

 

Need Help
  • MFelix's avatar
    MFelix
    4 years ago

    Hi harshadrokade 

     

    This has to do with context, your measure altough giving correct results when you have the measure with the year selection you get the result below:

     

    Has you can see the value returns the 25 however in the yearly value it gives blank, in the KPI card since you don't have the year (except on the trend) the calculation is done with blank value so 49 / blank is infinity

     

    If you redo your measure to:

    2nd most recent yar value = 
CALCULATE (
    SUM ( Sheet4[Value] ),
    FILTER (
       ALLSELECTED( Sheet4),
        Sheet4[Year]
            = CALCULATE (
                MAX ( Sheet4[Year] ),
                FILTER ( ALLSELECTED(Sheet4), Sheet4[Year] < MAX ( Sheet4[Year] ) )
            )
    ),
    Sheet4[Subject] = "A1"
)

    Using the ALLSELECTED this will solve your issue:

     

    You can also simplify the measure to:

     

    2nd most recent yar value =
SUMX (
    TOPN (
        1,
        FILTER (
            ALLSELECTED ( Sheet4 ),
            Sheet4[Year] < MAX ( Sheet4[Year] )
                && Sheet4[Subject] = "A1"
        ),
        Sheet4[Year], DESC
    ),
    Sheet4[Value]
)

     

1 Reply

  • MFelix's avatar
    MFelix
    Super User
    4 years ago

    Hi harshadrokade 

     

    This has to do with context, your measure altough giving correct results when you have the measure with the year selection you get the result below:

     

    Has you can see the value returns the 25 however in the yearly value it gives blank, in the KPI card since you don't have the year (except on the trend) the calculation is done with blank value so 49 / blank is infinity

     

    If you redo your measure to:

    2nd most recent yar value = 
CALCULATE (
    SUM ( Sheet4[Value] ),
    FILTER (
       ALLSELECTED( Sheet4),
        Sheet4[Year]
            = CALCULATE (
                MAX ( Sheet4[Year] ),
                FILTER ( ALLSELECTED(Sheet4), Sheet4[Year] < MAX ( Sheet4[Year] ) )
            )
    ),
    Sheet4[Subject] = "A1"
)

    Using the ALLSELECTED this will solve your issue:

     

    You can also simplify the measure to:

     

    2nd most recent yar value =
SUMX (
    TOPN (
        1,
        FILTER (
            ALLSELECTED ( Sheet4 ),
            Sheet4[Year] < MAX ( Sheet4[Year] )
                && Sheet4[Subject] = "A1"
        ),
        Sheet4[Year], DESC
    ),
    Sheet4[Value]
)