Help: Dynamic Measure w/ Percentages show Incorrect Subtotal

[itsme]

Thank you in advance for your help! I am using web data to show how many users initiated and completed a survey from the different web pages on our site (all data has been obfuscated). After shaping the tables in Query Editor, I have columns for Metric (Completions, Initiations, Page Visits, % Initiations > Completions, etc.), Values, Date, Web Page. Since we are talking about values and I also have a column called Values, I will spell Values with a capital V when referencing the column name.

I need to show a table (matrix visual) with the Pages as rows, Date as columns, Values and MoM % as values under the columns (as shown below). The data in the Values column of the query table is in decimal type - depending on the metric we are showing, values need to be either a number or percentage. I created a dynamic measure so the values change accordingly when I click the Metric I want to see from the slicer. The measure I put into the matrix view to show Values is 'Survey Values'. I used the SWITCH function to use a measure called Values % Surveys (shown below) for the values that contain percentages (% Initiations > Completions, % Page Visits > Completions, etc.), and ‘Values Surveys’ to show the non-percentage values (Completions, Page Visits, etc.).

PROBLEM: The subtotal line is incorrect when showing percentage values because the column is being summed. What I did as a temporary fix is remove the subtotal line and create measures for those values and put those measures into cards, as shown in the screenshots. The reason I did this is because if I replace the [Values % Surveys] portion within the 'Survey Values' measure with the new percentage measures currently in card views, the Survey Values measure does not show up in the matrix view when I slice by the desired Metric (as seen in the 2nd screenshot below). This only happens with percentage values - number values show up. Is there something wrong with my new percentage measures?

I really appreciate your time and any assistance you may provide! I feel like I’m so close but there's something missing. Thank you!

Survey Values =

SWITCH (

    TRUE (),

    LASTNONBLANK ( 'Web Page Surveys'[Metric], 1 )

        = "% Initiations > Completions", [Values % Surveys],

    LASTNONBLANK ( 'Web Page Surveys'[Metric], 1 )

        = "% Page Visits > Completions", [Values % Surveys],

    LASTNONBLANK ( 'Web Page Surveys'[Metric], 1 )

        = "% Page Visits > Initiations", [Values % Surveys],

    [Values Surveys]

)

Values % Surveys =

CALCULATE (

    ROUND ( SUM ( 'Web Page Surveys'[Value] ), 4 ),

    FILTER (

        ALLSELECTED ( 'Web Page Surveys'[Value] ),

        ISONORAFTER (

                'Web Page Surveys'[Value], MAX ( 'Web Page Surveys'[Value] ), DESC

        )

    )

)

    * 100

    & "%"

New percentage measures to replace [Values % Surveys] in ‘Survey Values’ measure (I only listed one of the 3 measures below to save space – they are all the same just filter different values):

% Intiations > Completions =

DIVIDE (

    CALCULATE (

        SUM ( 'Web Page Surveys'[Value] ),

        FILTER ( 'Web Page Surveys', 'Web Page Surveys'[Metric] = "Completions" )

    ),

    CALCULATE (

        SUM ( 'Web Page Surveys'[Value] ),

        FILTER ( 'Web Page Surveys', 'Web Page Surveys'[Metric] = "Initiations" )

    )

)

Screenshot of matrix when [Values % Surveys] are replaced with new percentage measures.

[v-yuta-msft]

Hi itsme,

Modify your measure like pattern below and check if it can work:

Values % Surveys =
CALCULATE (
    ROUND ( SUM ( 'Web Page Surveys'[Value] ), 4 ),
    FILTER (
        'Web Page Surveys',
        ISONORAFTER (
                'Web Page Surveys'[Value], MAX ( 'Web Page Surveys'[Value] ), DESC
        )
    ),
    ALLSELECTED ( 'Web Page Surveys'[Value] )
)
    * 100
    & "%"

Regards,

Jimmy Tao

[itsme]

Hi v-yuta-msft,

Thank you for your response, however, that did not work. It provides exactly the same result as before. To be honest, I don't even remember why 'Values % Surveys' contains the function ISONORAFTER - I just read up on it and I still don't understand what exactly it does.

Anyways, is there a way to just nest the new percentage measures into 'Survey Values', replacing 'Values % Surveys', so that the subtotal line is an actual division calculation instead of SUM? Below is an example of one of the new percentage measures. The only problem is it shows no data when nested into 'Survey Values' and slice by one of the percentage metrics.

% Intiations > Completions =
DIVIDE (
CALCULATE (
SUM ( 'Web Page Surveys'[Value] ),
FILTER ( 'Web Page Surveys', 'Web Page Surveys'[Metric] = "Completions" )
),
CALCULATE (
SUM ( 'Web Page Surveys'[Value] ),
FILTER ( 'Web Page Surveys', 'Web Page Surveys'[Metric] = "Initiations" )
)
)