cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Shamatix Member
Member

Missing rows when using SUM

Hi fellow users of Power Bi,

 

I have a table lets say it contains 3 colums A, B and C and has the Following Values

 

A  |B |C

Bo|1 |0

Bo|1 |0

Kik|2|3

-------

    |4|3

 

But today in power bi when I use a "SUM" it shows it as follows:

A  |B |C

Bo|2 |0

Kik|2|3

-------

    |4|3

 

I Know I can add a unique ID column to the table and that it will turn out correctly and show ALL the rows, but I find it to be an ugly solution. How can I have it shows the totals at the bottom AND all the rows, without making some "Hack solution" if you get me 🙂

Best regards

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Missing rows when using SUM

Hi @Shamatix , 

There is no method could achieve this directly. So one workaround is that you could show detail data in table and show total in Card like below

557.PNG

Best Regards,
Zoe Zhi

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

 

View solution in original post

9 REPLIES 9
Super User IV
Super User IV

Re: Missing rows when using SUM

What should Actually it display.

If you are looking for B =3

Try this

sumx(summarize(table,table[A],"_B",max(table[b])),[_B])

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601





Did I answer your question? Mark my post as a solution!
In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin


rajulshah Senior Member
Senior Member

Re: Missing rows when using SUM

Hello @Shamatix,

I assume you're plotting 'B' column in the visualization. Please right click on the column in Fields Pane and click on "Don't summarize".
power-bi-aggregate-sum.png

Shamatix Member
Member

Re: Missing rows when using SUM

If you use dont summarize I do not get the Total of the column at the bottom of the table which I want to have, but when I have "Sum" on, it hides some of the rows if I do not have a unique value..

rajulshah Senior Member
Senior Member

Re: Missing rows when using SUM

@Shamatix,

 

Can you please share your expected results again? I am unable to understand you.

Shamatix Member
Member

Re: Missing rows when using SUM

Well I want it to continue showing the total at the bottom of the table, so the total is correct.

My issue is that when you use a SUM on a column and the rows dont have a "Unique" value it hides some of the rows.

Shamatix Member
Member

Re: Missing rows when using SUM

I hope this screenshot illustrates what I want and what it does now

https://i.imgur.com/Qmc1eBY.png

Community Support Team
Community Support Team

Re: Missing rows when using SUM

Hi @Shamatix ,

I  think this is by design, if there is no unique id, it will show value as a group when you use aggregation or you will see all value but no total value. So I think current workaround is that you add an unique column before. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTJUitWBsIzArCQgywTMSoaLpUDUxQIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [name = _t, amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"name", type text}, {"amount", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1)
in
    #"Added Index"

543.PNG

 By the way, you also could submit this  requirement in power-bi-ideas   

Best Regards,
Zoe Zhi

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

 

Shamatix Member
Member

Re: Missing rows when using SUM

Well the table it self has a unique ID, but I dont want the unique ID shown in the table im trying to show the end user as they have nothing to use the ID column for?

 

Community Support Team
Community Support Team

Re: Missing rows when using SUM

Hi @Shamatix , 

There is no method could achieve this directly. So one workaround is that you could show detail data in table and show total in Card like below

557.PNG

Best Regards,
Zoe Zhi

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

 

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors