Forum Discussion

Frequent Visitor

10 months ago

Solved

Incorrect Total for Percent Change in Power BI Matrix Visual

Dear Community, I have a Matrix visual in Power BI where one of the columns represents the percent change in pageviews by site. The total percent change displayed at the bottom of the visual is ...

Need Help

KNP

10 months ago

Hi andrelee,

Thanks for the file. That makes a whole lot more sense with context.

The number of field parameter tables certainly impacts things.

Try this (might not be perfect, but I think it's close):

Create new measures...

__pv = 
    SWITCH(
        SELECTEDVALUE('Date Group'[Date Group Order])
        , 0, SUM(ga_pops[ga_pageviews])
        , 1, SUM(ga_pops[w_ga_pageviews])
        , 2, SUM(ga_pops[m_ga_pageviews])
        , 3, SUM(ga_pops[q_ga_pageviews])
        , 4, SUM(ga_pops[y_ga_pageviews])
        , SUM(ga_pops[ga_pageviews])
    )

__pv_pop = 
    SWITCH(
        SELECTEDVALUE('Date Group'[Date Group Order])
        , 0, SUM(ga_pops[dod_ga_pageviews])
        , 1, SUM(ga_pops[wow_ga_pageviews])
        , 2, SUM(ga_pops[mom_ga_pageviews])
        , 3, SUM(ga_pops[qoq_ga_pageviews])
        , 4, SUM(ga_pops[yoy_ga_pageviews])
        , SUM(ga_pops[dod_ga_pageviews])
    )

__pv_%_Δ = 
    DIVIDE(
        [__pv] - [__pv_pop]
        , [__pv_pop]
        , 0
    ) * 100.0

These are then used in the final switch calc...

_sw_pv_%_Δ = 
    VAR __is_total =
        NOT ISINSCOPE('sites_py'[name])
        && NOT ISINSCOPE('sites_py'[o&o])
        && NOT ISINSCOPE('ga_pops'[device_type])
    VAR __group_order =
        SELECTEDVALUE('Site Group'[Site Group Order])
    VAR __result =
        IF(
            __is_total
            , SWITCH(
                __group_order
                , 0
                    , AVERAGEX(
                        VALUES('sites_py'[name])
                        , CALCULATE([__pv_%_Δ])
                    )
                , 1
                    , AVERAGEX(
                        VALUES('sites_py'[o&o])
                        , CALCULATE([__pv_%_Δ])
                    )
                , 2
                    , AVERAGEX(
                        VALUES('ga_pops'[device_type])
                        , CALCULATE([__pv_%_Δ])
                    )
                , BLANK()
            )
            , [__pv_%_Δ]
        )
    RETURN
        __result

I haven't had time to test 100%, I need to do my paid job 😄. Let me know how it goes or if you have questions.

DaleT

Resolver II

10 months ago

Hi,

The cause is the different context. 2.18, 8.85 and 5.51 are aggregated over different dimensions. The solution can be like this:

AVERAGEX (
    values(table[name]),
    DIVIDE (
        SUM ( ga_pops[y_ga_pageviews] ) - SUM ( ga_pops[yoy_ga_pageviews] ),
        SUM ( ga_pops[yoy_ga_pageviews] ),
        0
    ) * 100.0
)

andrelee

Frequent Visitor

10 months ago

Thank you for the clarification! I marked the solution as resolved a bit too early.
I added the AVERAGEX to my original DAX, but the total's average values are still incorrect.
What else could be causing this?

sw_ga_pop_pageviews = 
AVERAGEX(
    VALUES(sites_py[name]),
    SWITCH(
        SELECTEDVALUE('Date Group'[Date Group Order]),
        0, 
            DIVIDE(
                SUM(ga_pops[ga_pageviews]) - SUM(ga_pops[dod_ga_pageviews]), 
                SUM(ga_pops[dod_ga_pageviews]),
                0
            ) * 100.0,
        1, 
            DIVIDE(
                SUM(ga_pops[w_ga_pageviews]) - SUM(ga_pops[wow_ga_pageviews]), 
                SUM(ga_pops[wow_ga_pageviews]),
                0
            ) * 100.0,
        2, 
            DIVIDE(
                SUM(ga_pops[m_ga_pageviews]) - SUM(ga_pops[mom_ga_pageviews]), 
                SUM(ga_pops[mom_ga_pageviews]),
                0
            ) * 100.0,
        3, 
            DIVIDE(
                SUM(ga_pops[q_ga_pageviews]) - SUM(ga_pops[qoq_ga_pageviews]), 
                SUM(ga_pops[qoq_ga_pageviews]),
                0
            ) * 100.0,
        4, 
            DIVIDE(
                SUM(ga_pops[y_ga_pageviews]) - SUM(ga_pops[yoy_ga_pageviews]), 
                SUM(ga_pops[yoy_ga_pageviews]),
                0
            ) * 100.0
        )
)

KNP

Super User

10 months ago

Hi andrelee,

I've removed the solution so that others won't be deterred from providing input. Accept a new one once it's solved.

Can you please provide a screenshot of your model? I don't think we have enough information to solve this currently.

Also, some sample data pasted as data (not a screenshot) would be helpful.

Also, let's see if the slayer of measure totals (Greg_Deckler) has any input. 😉

Greg_Deckler
Community Champion
10 months ago
KNP Just the standard input. First, please vote for this idea: https://community.fabric.microsoft.com/t5/Fabric-Ideas/Matrix-Table-grand-totals-with-Measures/idi-p/4475982

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

andrelee

Frequent Visitor

10 months ago

Thanks for your help, KNP. Here is a link to the pbix file.

KNP

Super User

10 months ago

Hi andrelee,

Thanks for the file. That makes a whole lot more sense with context.

The number of field parameter tables certainly impacts things.

Try this (might not be perfect, but I think it's close):

Create new measures...

__pv = 
    SWITCH(
        SELECTEDVALUE('Date Group'[Date Group Order])
        , 0, SUM(ga_pops[ga_pageviews])
        , 1, SUM(ga_pops[w_ga_pageviews])
        , 2, SUM(ga_pops[m_ga_pageviews])
        , 3, SUM(ga_pops[q_ga_pageviews])
        , 4, SUM(ga_pops[y_ga_pageviews])
        , SUM(ga_pops[ga_pageviews])
    )

__pv_pop = 
    SWITCH(
        SELECTEDVALUE('Date Group'[Date Group Order])
        , 0, SUM(ga_pops[dod_ga_pageviews])
        , 1, SUM(ga_pops[wow_ga_pageviews])
        , 2, SUM(ga_pops[mom_ga_pageviews])
        , 3, SUM(ga_pops[qoq_ga_pageviews])
        , 4, SUM(ga_pops[yoy_ga_pageviews])
        , SUM(ga_pops[dod_ga_pageviews])
    )

__pv_%_Δ = 
    DIVIDE(
        [__pv] - [__pv_pop]
        , [__pv_pop]
        , 0
    ) * 100.0

These are then used in the final switch calc...

_sw_pv_%_Δ = 
    VAR __is_total =
        NOT ISINSCOPE('sites_py'[name])
        && NOT ISINSCOPE('sites_py'[o&o])
        && NOT ISINSCOPE('ga_pops'[device_type])
    VAR __group_order =
        SELECTEDVALUE('Site Group'[Site Group Order])
    VAR __result =
        IF(
            __is_total
            , SWITCH(
                __group_order
                , 0
                    , AVERAGEX(
                        VALUES('sites_py'[name])
                        , CALCULATE([__pv_%_Δ])
                    )
                , 1
                    , AVERAGEX(
                        VALUES('sites_py'[o&o])
                        , CALCULATE([__pv_%_Δ])
                    )
                , 2
                    , AVERAGEX(
                        VALUES('ga_pops'[device_type])
                        , CALCULATE([__pv_%_Δ])
                    )
                , BLANK()
            )
            , [__pv_%_Δ]
        )
    RETURN
        __result

I haven't had time to test 100%, I need to do my paid job 😄. Let me know how it goes or if you have questions.

DaleT

Resolver II

10 months ago

This is caused by the row context introduced by AVERAGEX. The easy fix is like below. I would suggest you use variables to reduce repetitions and to improve performance.

sw_ga_pop_pageviews =
AVERAGEX (
    VALUES ( sites_py[name] ),
    CALCULATE (
        SWITCH (
            SELECTEDVALUE ( 'Date Group'[Date Group Order] ),
            0,
                DIVIDE (
                    SUM ( ga_pops[ga_pageviews] ) - SUM ( ga_pops[dod_ga_pageviews] ),
                    SUM ( ga_pops[dod_ga_pageviews] ),
                    0
                ) * 100.0,
            1,
                DIVIDE (
                    SUM ( ga_pops[w_ga_pageviews] ) - SUM ( ga_pops[wow_ga_pageviews] ),
                    SUM ( ga_pops[wow_ga_pageviews] ),
                    0
                ) * 100.0,
            2,
                DIVIDE (
                    SUM ( ga_pops[m_ga_pageviews] ) - SUM ( ga_pops[mom_ga_pageviews] ),
                    SUM ( ga_pops[mom_ga_pageviews] ),
                    0
                ) * 100.0,
            3,
                DIVIDE (
                    SUM ( ga_pops[q_ga_pageviews] ) - SUM ( ga_pops[qoq_ga_pageviews] ),
                    SUM ( ga_pops[qoq_ga_pageviews] ),
                    0
                ) * 100.0,
            4,
                DIVIDE (
                    SUM ( ga_pops[y_ga_pageviews] ) - SUM ( ga_pops[yoy_ga_pageviews] ),
                    SUM ( ga_pops[yoy_ga_pageviews] ),
                    0
                ) * 100.0
        )
    )
)

Forum Discussion

Incorrect Total for Percent Change in Power BI Matrix Visual

Related content

percent change

YOY change incorrect when prior year value is negative

percent change by rows

Incorrect turnover total

Average of percents calculation incorrect in bar graph