Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Using slicer as variable input to use in measure calculation

Hi everyone,

 

Stuck with something I hope is quite simple... I have a measure that calculates the % of grades >= 4. This is derived from a count of grades / no. of students. 

 

I want to create a measure that compares the current report cycle against the previous one. To create flexibility (plus I couldn't find another method) I've created two disconnected slicers to use as variable inputs for the report cycles.

 

I then want to apply the variables in the calculation (current report cycle % >=4) minus (previous report cycle % >= 4)

 

My variable measures look like this:

CurrentReportCycle =
VAR CurrentSelection = SELECTEDVALUE(Disconnected_ReportCycleName[Current Report Cycle], "All")
Return
CurrentSelection
 
PreviousReportCycle =
VAR CurrentSelection = SELECTEDVALUE(Disconnected_ReportCycleName[Previous Report Cycle], "All")
Return
CurrentSelection
 
And my %>=4 measure:
% 4+ (IB) = [Count of 4+ (IB)]/[No. of pupils (IB)]
 
And the count measure:
Count of 4+ (IB) =
CALCULATE(
COUNT(Report_Data[Attainment Track]),
Report_Data[Attainment Track]>=4, Report_Data[Course Name]="IB")
 
Any help appreciated!
Matt
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi Anonymous 

    Create a new table

    new table = VALUES(Sheet2[cycle])

    Create calculated columns in this table

    year1 = LEFT([cycle],4)

index = RIGHT([cycle],1)

rank =
RANKX (
    'new table',
    RANKX ( 'new table', [year1],, ASC, DENSE )
        + DIVIDE (
            RANKX ( 'new table', [index],, ASC, DENSE ),
            ( COUNTROWS ( 'new table' ) + 1 )
        ),
    ,
    ASC,
    DENSE
)

     

    Create measures in your table

    count_4 = CALCULATE(COUNT(Sheet2[track]),FILTER(ALLSELECTED(Sheet2),Sheet2[track]>=4))

no. of students = CALCULATE(SUM(Sheet2[no.students]),ALLSELECTED(Sheet2))

selected = SELECTEDVALUE('new table'[cycle])

selected -1 =
VAR previous_ =
    CALCULATE (
        MAX ( 'new table'[rank] ) - 1,
        FILTER ( 'new table', [cycle] = SELECTEDVALUE ( 'new table'[cycle] ) )
    )
RETURN
    CALCULATE (
        MAX ( 'new table'[cycle] ),
        FILTER ( ALL ( 'new table' ), 'new table'[rank] = previous_ )
    )


c_% =
VAR c_count =
    CALCULATE (
        [count_4],
        FILTER ( ALLSELECTED ( Sheet2 ), Sheet2[cycle] = [selected] )
    )
VAR c_no =
    CALCULATE (
        [no. of students],
        FILTER ( ALLSELECTED ( Sheet2 ), Sheet2[cycle] = [selected] )
    )
RETURN
    c_count / c_no


p_% =
VAR p_count =
    CALCULATE (
        [count_4],
        FILTER ( ALLSELECTED ( Sheet2 ), Sheet2[cycle] = [selected -1] )
    )
VAR p_no =
    CALCULATE (
        [no. of students],
        FILTER ( ALLSELECTED ( Sheet2 ), Sheet2[cycle] = [selected -1] )
    )
RETURN
    p_count / p_no


c-p = [c_%]-[p_%]
    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi, i suggest you to use time-intelligence functions to calculate measures of past periods, you can use functions as DATEADD, SAMEPERIODLASTYEAR, etc. If you don't have a date column and just a "cycle" column with integers values like 1,2,3,4... then, you must use ALL or ALLEXCEPT functions to create your measure.

    for example if you have a measure called "CountAnything", then you must create this measure for the past cycle:

    CountAnythingPast = CALCULATE(CountAnything,Filter(ALL(table),table[cycle]=table[cycle]-1))

    if you want clearest help, public your tables and how do you want the results

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you for your help. I have replicated the measure you suggested but it isn't working. How does the measure know what the current report cycle name is?

       

      I've written this which works however it would be much better if I can make it dynamic.

       

      Current - previous % 4+ = CALCULATE([% 4+ (IGCSE)],Report_Data[Report Cycle Name]="201920.1")-CALCULATE([% 4+ (IGCSE)],Report_Data[Report Cycle Name]="201819.6")

       

      The report cycle names are 201819.1 through to 6 then 201920.1 to 6 and so on... 

       

      I'm pretty new to this so any help or pointing me in the right direction would be superb.

      Thanks

      Matt

      • v-juanli-msft's avatar
        v-juanli-msft
        Community Support
        6 years ago

        Hi Anonymous 

        Create a new table

        new table = VALUES(Sheet2[cycle])

        Create calculated columns in this table

        year1 = LEFT([cycle],4)

index = RIGHT([cycle],1)

rank =
RANKX (
    'new table',
    RANKX ( 'new table', [year1],, ASC, DENSE )
        + DIVIDE (
            RANKX ( 'new table', [index],, ASC, DENSE ),
            ( COUNTROWS ( 'new table' ) + 1 )
        ),
    ,
    ASC,
    DENSE
)

         

        Create measures in your table

        count_4 = CALCULATE(COUNT(Sheet2[track]),FILTER(ALLSELECTED(Sheet2),Sheet2[track]>=4))

no. of students = CALCULATE(SUM(Sheet2[no.students]),ALLSELECTED(Sheet2))

selected = SELECTEDVALUE('new table'[cycle])

selected -1 =
VAR previous_ =
    CALCULATE (
        MAX ( 'new table'[rank] ) - 1,
        FILTER ( 'new table', [cycle] = SELECTEDVALUE ( 'new table'[cycle] ) )
    )
RETURN
    CALCULATE (
        MAX ( 'new table'[cycle] ),
        FILTER ( ALL ( 'new table' ), 'new table'[rank] = previous_ )
    )


c_% =
VAR c_count =
    CALCULATE (
        [count_4],
        FILTER ( ALLSELECTED ( Sheet2 ), Sheet2[cycle] = [selected] )
    )
VAR c_no =
    CALCULATE (
        [no. of students],
        FILTER ( ALLSELECTED ( Sheet2 ), Sheet2[cycle] = [selected] )
    )
RETURN
    c_count / c_no


p_% =
VAR p_count =
    CALCULATE (
        [count_4],
        FILTER ( ALLSELECTED ( Sheet2 ), Sheet2[cycle] = [selected -1] )
    )
VAR p_no =
    CALCULATE (
        [no. of students],
        FILTER ( ALLSELECTED ( Sheet2 ), Sheet2[cycle] = [selected -1] )
    )
RETURN
    p_count / p_no


c-p = [c_%]-[p_%]
        Best Regards
        Maggie
        Community Support Team _ Maggie Li
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.