Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
munchkin666
Helper II
Helper II

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. 

munchkin666_0-1699017961843.png

However the total doesnt sum it correctly. Could anyone please advice why 50 is not included? I would really apprecaite any help

munchkin666_1-1699018093878.png

 

1 ACCEPTED SOLUTION

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])
Ritaf1983_0-1699293257964.png

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

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

View solution in original post

9 REPLIES 9
munchkin666
Helper II
Helper II

@Ritaf1983  thank you very much! I will have a look and let you know.

Ritaf1983
Super User
Super User

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...

 

 

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

 

 

 

 

 

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

@Ritaf1983  Thank you fro the links, unfortunately, it didnt work for me. It still excludes my blanks

munchkin666_0-1699268077065.png

 

Hi @munchkin666 
Please share some data to work with.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

@Ritaf1983  I am unable to add it, it keeps coming bakc with "your post has been changed because invalid html was found in the message body. the invalid html has been removed. please review the message and submit the message when you are satisfied.". I have tried to split the table, but its too big. Basically, I have lets say 3 groups:A,B,C. Group B didint have any data for 2022, but started to receive it in 2023. I did a calullation IF(ISBLANK(Result),50, Result/12). But for some reasons, PowerBI doesnt want to sum it in the totals. It only sums up A and B, although C (50) is listed there 😞

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

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

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])
Ritaf1983_0-1699293257964.png

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

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile
TheBishopEmpire
Frequent Visitor

I think I'm having a similar thing happen with a more basic example. My matrix column total is displaying one less than if I manually add the column values. I will be posting my example as well.  

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.