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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
byWing
Frequent Visitor

Table - Total for column and row is wrong

Hi All,

 

In my table visual, I wish to show the total no. of people by row and by column. I know that Power BI has this issue with totals. 

 

I have been reading all the different posts and all,  but as I am new to DAX, I am still unable to find out how do I resolve this. =( Something easy in Excel becomes impossible in Power BI.

 

Please, if anyone can guide me on how to write the DAX to recaclulate the total and then how to put it back into the table visual, I will be much appreciative.

 

Note: Place-A, Place-B, Place-C are all from a column called [Place].

 

Thank you!

byWing_0-1679625063443.png

 

1 ACCEPTED SOLUTION
byWing
Frequent Visitor

Hi all,

 

I want to thank @rajulshah @Ahmedx for their responses.


I tried again and realized what I need is distinct count total. 

So I used the solution at Solved: COUNT DISTINCT SHOWS WRONG GRAND TOTAL - Microsoft Power BI Community and solved my own case.

Thank you again for the strong support!

View solution in original post

5 REPLIES 5
byWing
Frequent Visitor

Hi all,

 

I want to thank @rajulshah @Ahmedx for their responses.


I tried again and realized what I need is distinct count total. 

So I used the solution at Solved: COUNT DISTINCT SHOWS WRONG GRAND TOTAL - Microsoft Power BI Community and solved my own case.

Thank you again for the strong support!
Ahmedx
Super User
Super User

 

total = SUMX ( VALUES ( Table [ teams ] ) , [ Place] )

 

 

byWing
Frequent Visitor

Thank you for your reply @rajulshah 

 

I wish to clarify what is the [Measure] in your Dax Measure please?

 

Perhaps what I didn't share is there is another column called [ID] which is all the participant names.

 

E.g. In Place-A, E&I/ELECT Team, there are Ian, Jon, May, Lee, Chan, Dave, thus giving the distinct count as 6.

 

Not sure if this make sense, but pls feel free to advise if you need more info.

 

I appreciate your time and patience. Thank you.

 

byWing_0-1679647958944.png

 

@byWing 

Measure is the one that you are currently using and in VALUES it should be the column that you are plotting in Row Headers.

rajulshah
Super User
Super User

@byWing 

 

You can use the following DAX measure:

Totals = SUMX ( VALUES ( Table [ Place ] ) , [Measure] )

 

And plot this measure in the matrix and let me know if this didn't work.

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors