Forum Discussion

munchkin666's avatar
munchkin666
Helper II
2 years ago
Solved

table doesnt sum correctly

hi, I have a quick question. I have one table with the average measure for different components. I add 50 if the value is blank.  However the total doesnt sum it correctly. Could anyone please...
Need Help
  • Ritaf1983's avatar
    Ritaf1983
    2 years ago

    Hi  munchkin666
    Total in power bi doesn't summarize the visible values automatically.
    You need to fix it "manually".Please refer to the linked tutorials :
    https://www.youtube.com/watch?v=yw0QHu9V4UQ&t=773s

    https://www.youtube.com/watch?v=O6qUiICLxLg

    I answered a few days ago to question  with a similar scenario including a sample pbix You can take a look here:

    https://community.fabric.microsoft.com/t5/Desktop/Show-the-Average-Total-for-Avg-calculation-and-Sum-Total-for-Sum/m-p/3506424#M1159112

     

     

    There is also the idea of Greg_Deckler  about this issue, please vote for it :https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

     

     If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

     

     

     

     

     

  • Ritaf1983's avatar
    Ritaf1983
    2 years ago

    Hi munchkin666 
    Your scenario is much more complex than regular total problems because you want to include values of groups that don't in the filtered period.
    You will need 2 measures :
    1. Kind of prepartion

     

    TotalValue2022 =
    VAR GroupsIn2022 = SUMMARIZE(FILTER('table', YEAR('table'[Date]) = 2022), 'table'[Group])
    VAR AllGroups = VALUES('table'[Group])
    VAR GroupsNotIn2022 = EXCEPT(AllGroups, GroupsIn2022)
    VAR ResultTable =
        ADDCOLUMNS(
            AllGroups,
            "Value2022",
            IF(
                COUNTROWS(FILTER(GroupsIn2022, 'table'[Group] = [Group])) > 0,
                IF([Group] = "Other", 50, SUMX(FILTER('table', 'table'[Group] = [Group] && YEAR('table'[Date]) = 2022), 'table'[Value])/12),
                50
            )
        )
    RETURN
    SUMX(ResultTable, [Value2022])
    2. To put on the table :
    TotalValueByGroup = SUMX(SUMMARIZE('table', 'table'[Group], "TotalValue", [TotalValue2022]), [TotalValue])

    Don't ask how I did it; I'm not sure that I can explain, and luck did its work here too. 🙂

    Pbix is attached

     If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

Ritaf1983's avatar
Ritaf1983
Super User
2 years ago

Hi munchkin666 
Just save it in onedrive/wetransfer/dropbox/something else and share via link.

  • Ritaf1983's avatar
    Ritaf1983
    Super User
    2 years ago

    Hi munchkin666 
    Your scenario is much more complex than regular total problems because you want to include values of groups that don't in the filtered period.
    You will need 2 measures :
    1. Kind of prepartion

     

    TotalValue2022 =
    VAR GroupsIn2022 = SUMMARIZE(FILTER('table', YEAR('table'[Date]) = 2022), 'table'[Group])
    VAR AllGroups = VALUES('table'[Group])
    VAR GroupsNotIn2022 = EXCEPT(AllGroups, GroupsIn2022)
    VAR ResultTable =
        ADDCOLUMNS(
            AllGroups,
            "Value2022",
            IF(
                COUNTROWS(FILTER(GroupsIn2022, 'table'[Group] = [Group])) > 0,
                IF([Group] = "Other", 50, SUMX(FILTER('table', 'table'[Group] = [Group] && YEAR('table'[Date]) = 2022), 'table'[Value])/12),
                50
            )
        )
    RETURN
    SUMX(ResultTable, [Value2022])
    2. To put on the table :
    TotalValueByGroup = SUMX(SUMMARIZE('table', 'table'[Group], "TotalValue", [TotalValue2022]), [TotalValue])

    Don't ask how I did it; I'm not sure that I can explain, and luck did its work here too. 🙂

    Pbix is attached

     If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly