DAX Expression To Prevent Cards From Summing Percentages

[bwhitehorne]

Hello,

I have a dataset formatted like this:

Customer Group	Customer	2022 Revenue YTD	2021 Revenue YTD	Delta	% Growth over 2021
Group 1	Customer A	100,000	95,000	5,000	5.26%
Group 1	Customer C	50,000	65,000	-15,000	-23.07%
Group 2	Customer F	75,000	50,000	25,000	50%
Group 2	Customer D	300,000	298,000	2,000	0.67%

I am trying to display the "% Growth over 2021" in a card, with a slicer for "Customer". Currently when I have one customer selected in the slicer, the % Growth over 2021 field displays correctly in the card. When I select more than one customer, the card sums the percentages and displays it incorrectly. The goal would be for it to give the percentage based on the TOTALS when multiple customers are selected. Is there any work around for this with DAX?

[v-xiaosun-msft · Accepted solution]

Hi @bwhitehorne ,

According to your description, here is my solution.

Create a measure.

Total % Growth over 2021 =
DIVIDE (
    SUM ( 'Table'[2022 Revenue YTD] ) - SUM ( 'Table'[2021 Revenue YTD] ),
    SUM ( 'Table'[2021 Revenue YTD] )
)

Display the "Total % Growth over 2021" in a card, with a slicer for “Customer”.
Select multiple customers that you need in the slicer.

Finally, the card will display different results of the percentage based on the TOTALS according to multiple customers.

Best Regards,
Community Support Team _ xiaosun

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

[amitchandak]

@bwhitehorne , Try like

if these two are measures - 2022 Revenue YTD 2021 Revenue YTD; then formula should be

Measure for % Growth over 2021

divide([2022 Revenue YTD] - [2021 Revenue YTD], [2021 Revenue YTD])

If they are columns then

Measure for % Growth over 2021

divide(Sum(Table[2022 Revenue YTD]) - Sum(Table[2021 Revenue YTD]), Sum(Table[2021 Revenue YTD]))