cancel
Showing results for
Did you mean:
Highlighted
Member

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

I have a table as follows:

and I have created a visual in PBI as follows:

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

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

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.

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

Established Member

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

I have used some dummy data in here

thanks

Chandeep

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.