Calculation Groups with ranking

[Myrto]

Hi All,

I'm straggling with the calculation groups 'cause I'm trying to add three total columns, one for Current Year, one for Last Year and the column with the variation between these two. My table has also months columns because I need to show the evolution of my sales over the last 12 months. I followed the very helpful video https://www.youtube.com/watch?v=90X5_tISxIY and I was able to create the same scenario. The problem comes when I apply TOPN sales feature to filter my data. For the TOPN I have created a dynamic measure that allowes me to calculate the ranking on totals based on the selected measure (as I have also created a parameter field to select 3 different measure). On top of this, I have created a numeric field slicer in order to select the desired TOPN from 1 to 20 and linked this slicer to a measure called "visible rows" that gives me 0 for all sales above the ranking 20 and used this measure in the visualisation filter to visualize only the TOPN selected from the slicer. Until here, everything works perfectly: I see my TOPN customers by month and the totals are matching with the visualisation.

Now, I need to add those three additional columns and to do so I have created a calculation group with three items: CY, LY and CY vs LY %:

CY YTD =

VAR Ranking = [Rank]

VAR TopNValue = 'TopN'[TopN Value]

RETURN

IF(

    INT(Ranking <= TopNValue),

    IF(

        NOT (ISINSCOPE(DIM_DAY[Year-Month Name])),

        SUMX(

            SUMMARIZE(

                Volumes_Clients_Mensuel,

                Volumes_Clients_Mensuel[Billing Account Name]

            ),

            CALCULATE(

            SELECTEDMEASURE(),

            DATESYTD(DIM_DAY[DATE_REF])

            )

        )

    )

)

LY YTD =

VAR Ranking = [Rank]

VAR TopNValue = 'TopN'[TopN Value]

RETURN

IF(

    INT(Ranking <= TopNValue),

    IF(

        NOT (ISINSCOPE(DIM_DAY[Year-Month Name])),

        SUMX(

            VALUES(Volumes_Clients_Mensuel[Billing Account Name]),

            CALCULATE(

            SELECTEDMEASURE(),

            SAMEPERIODLASTYEAR(DIM_DAY[DATE_REF])

            )

        )

    )

)

CY vs LY % =

VAR Ranking = [Rank]

VAR TopNValue = 'TopN'[TopN Value]

VAR CY =

    SUMX(

        VALUES(Volumes_Clients_Mensuel[Billing Account Name]),

        CALCULATE(

        SELECTEDMEASURE(),

        DATESYTD(DIM_DAY[DATE_REF])

        )

    )

VAR LY =

    SUMX(

        VALUES(Volumes_Clients_Mensuel[Billing Account Name]),

        CALCULATE(

        SELECTEDMEASURE(),

        SAMEPERIODLASTYEAR(DIM_DAY[DATE_REF])

        )

    )

RETURN

IF(

    CY && LY && NOT(ISINSCOPE(DIM_DAY[Year-Month Name]))

    && INT(Ranking <= TopNValue)

    ,

    FORMAT(DIVIDE(CY,LY) - 1,"Percent")

)

Unfortunately it doesn't work, because when I drag the calculation group into the columns of my visualisation (I put them on top of my calendar months), the matrix doesn't allow me to visualize all the levels at once and more over the filter of the TOPN applies to these new columns but not anymore in the lowest level. If I try to act on the "visible rows" that I put in the filters it looks like is not active anymore, it's freezed. Moreover, even if the filter is applied to the three additional columns, the total shown is wrong as it's computing the total of all sales and not only the visualized customers. How can I solve this ? 😞 

[AnalyticsWizard · Accepted solution]

@Myrto 

Certainly! To adjust your existing measure and handle the scenario where people have more hours remaining than available, you can modify your calculation. Let’s create a new measure that takes into account the capped hours for each period:

Remaining Hours = 
VAR NovDecCap = MIN(200, [Nov-Dec Cap Hours])
VAR JanOctCap = MIN(200, [Jan-Oct Cap Hours])
VAR TotalCap = 350
VAR UsedHours = NovDecCap + JanOctCap
VAR Remaining = TotalCap - UsedHours
RETURN
IF(Remaining < 0, 0, Remaining)

Explanation:

• We calculate the capped hours for both Nov-Dec and Jan-Oct.
• We sum up the used hours.
• We calculate the remaining hours by subtracting the used hours from the total cap (350).
• If the remaining hours are negative (i.e., more used than available), we display 0; otherwise, we show the actual remaining hours.

Apply this new measure to your matrix, and it should handle the scenario correctly. If you have similar measures, you can follow a similar approach.

Feel free to adapt this logic to other measures as needed. Let me know if you need further assistance! 😊