Forum Discussion

andrelee's avatar
andrelee
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's avatar
    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.

     

KNP's avatar
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. 😉

 

  • KNP's avatar
    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.

     

    • andrelee's avatar
      andrelee
      Frequent Visitor
      10 months ago

      Thank you, KNP , for taking the time to provide the solution! I am going to test it in the next few days and "Accept as a Solution" or ask follow-up questions.