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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Calculating Sum of each column separately and show it on a card?

I have a table as follows:

SumColumnsExcel.JPG

and I have created a visual in PBI as follows:

SumColumns.JPG

What I need is to show the total of Baby 1, Baby 2, Father 1, etc. on a "Card" visual in a way that shows each one when the user chooses it from the legend of the visual. If Card is not the best one, what is your suggestion?

1 ACCEPTED SOLUTION
v-xicai
Community Support
Community Support

  Hi @Anonymous ,

 

you can select the columns Baby 1, Baby 2, Father 1, etc. in Query Editor, then unpivot these column to row data like picture below, rename the column Attribute as "Role" (content will be Baby 1, Baby 2, Father 1, etc ), click "Close & Apply".

 

For example:

 

11.png12.png

 

 

 

 

 

Then create measure using DAX below.

 

Sum for roles= CALCULATE(SUM(Table1[Value]),FILTER(Table1, Table1[Role]=max(Table1[Role])))

 

Finally, you can drag the field Role and Sum for roles into Table visual to display the result. Maybe you need to redesign your Stacked bar chart according the new table structure.

 

Best Regards,

Amy

 

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

4 REPLIES 4
Ashish_Mathur
Super User
Super User

Hi,

You may download my solution PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
ChandeepChhabra
Impactful Individual
Impactful Individual

@Anonymous 

Please see if this fits your solution - DOWNLOAD the File

Untitled.gif

 

I have used some dummy data in here

thanks

Chandeep

v-xicai
Community Support
Community Support

  Hi @Anonymous ,

 

you can select the columns Baby 1, Baby 2, Father 1, etc. in Query Editor, then unpivot these column to row data like picture below, rename the column Attribute as "Role" (content will be Baby 1, Baby 2, Father 1, etc ), click "Close & Apply".

 

For example:

 

11.png12.png

 

 

 

 

 

Then create measure using DAX below.

 

Sum for roles= CALCULATE(SUM(Table1[Value]),FILTER(Table1, Table1[Role]=max(Table1[Role])))

 

Finally, you can drag the field Role and Sum for roles into Table visual to display the result. Maybe you need to redesign your Stacked bar chart according the new table structure.

 

Best Regards,

Amy

 

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

 

 

 

 

Anonymous
Not applicable

Thanks @v-xicai !

Although it showed me a new way of calculating the total of values in my chart but it is not my answer! Specially i need to use stacked column chart as it is the best visual for showing stack of values which are changin across different fruits on my visual!

Can you clearly explain what this formula tries to do?It sounds like it is summing up the total of totals or counts it up neither is my answer! @v-xicai 

Also I tried to use it on a card and make a relationship with my matrix of data, alothough the relationship could be creatd but it doesn't show the right value on it!

@v-xicai Please advise what could be the best visual to furnish my need?

 

Thanks

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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