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

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

Accepted Solutions
v-xicai New Contributor
New Contributor

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

  Hi @bestmbaman ,

 

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.

 

 

 

 

4 REPLIES 4
v-xicai New Contributor
New Contributor

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

  Hi @bestmbaman ,

 

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.

 

 

 

 

bestmbaman Member
Member

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

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

 

ChandeepChhabra Established Member
Established Member

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

@bestmbaman 

Please see if this fits your solution - DOWNLOAD the File

Untitled.gif

 

I have used some dummy data in here

thanks

Chandeep

Super User
Super User

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

Hi,

You may download my solution PBI file from here.

Hope this helps.

Untitled.png