cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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.

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

1 ACCEPTED SOLUTION
Super User

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

9 REPLIES 9
Helper II

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

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 :

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

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

Helper II

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

Super User

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.

Helper II

@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 😞

Super User

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

Helper II
Super User

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

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.

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors