cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Calculated measure impacting Waterfall Chart

Good afternoon,

I have been trying with my colleague to solve this seemingly simple problem for the last two days and it is absolutely driving us crazy.

The problem:

I am trying to create a Watefall chart which shows the difference between Total Sales by KG for Year over Year by Item Category. I have included links and screenshots below.

I'm trying to show how much of an impact Fruit/KG and Vegetables/KG is having on Total Sales KG. In the screenshot below I show that the gap between 2018 and 2019 is 242 (938-696). I'd like the breakdown to show how much of that difference is due to Fruits and how much is due to Vegetables.

Instead, because Total Sales KG is a calculated measure:

Sales Per Kilogram = DIVIDE(SUM(Sheet1[Sales Dollars]),SUM(Sheet1[Weight]))

The waterfall is showing Fruit Year over Year difference and Vegetables Year over Year difference without weighting it on the impact on total.

Sample Data

PowerBI report

Thank you if anyone is able to help us!

Regards,

Gareth.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Calculated measure impacting Waterfall Chart

Hi @jazking123 ,

After my testing, you could edit your measure like the following DAX:

```Sales Per Kilogram =
CALCULATE (
DIVIDE (
CALCULATE (
SUM ( Sheet1[Sales Dollars] ),
ALLSELECTED ( Sheet1[Item Description] )
),
CALCULATE ( SUM ( Sheet1[Weight] ), ALLSELECTED ( Sheet1[Item Category] ) )
)
)```

Here is the result.

If this post helps, then please consider Accept it as the solution to help the other members find it.
4 REPLIES 4
Frequent Visitor

## Incorrect breakdown on Waterfall when using a calculated measure.

Good afternoon,

I have been trying with my colleague to solve this seemingly simple problem for the last two days and it is absolutely driving us crazy.

The problem:

I am trying to create a Watefall chart which shows the difference between Total Sales by KG for Year over Year by Item Category. I have included links and screenshots below.

I'm trying to show how much of an impact Fruit/KG and Vegetables/KG is having on Total Sales KG. In the screenshot below I show that the gap between 2018 and 2019 is 242 (938-696). I'd like the breakdown to show how much of that difference is due to Fruits and how much is due to Vegetables.

Instead, because Total Sales KG is a calculated measure:

Sales Per Kilogram = DIVIDE(SUM(Sheet1[Sales Dollars]),SUM(Sheet1[Weight]))

The waterfall is showing Fruit Year over Year difference and Vegetables Year over Year difference without weighting it on the impact on total.

Sample Data

PowerBI report

Thank you if anyone is able to help us!

Regards,

Gareth.

Community Support Team

## Re: Calculated measure impacting Waterfall Chart

Hi @jazking123 ,

After my testing, you could edit your measure like the following DAX:

```Sales Per Kilogram =
CALCULATE (
DIVIDE (
CALCULATE (
SUM ( Sheet1[Sales Dollars] ),
ALLSELECTED ( Sheet1[Item Description] )
),
CALCULATE ( SUM ( Sheet1[Weight] ), ALLSELECTED ( Sheet1[Item Category] ) )
)
)```

Here is the result.

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

## Re: Calculated measure impacting Waterfall Chart

Thank you so much!

The solution works well. You have saved our sanity.

Frequent Visitor

## Re: Calculated measure impacting Waterfall Chart

@v-eachen-msft Thank you very much for your help, it works perfectly. Quick question, I've now set up my sheet so that Sales Dollars and Weight are now attributes in the same column rather than seperate columns. I thought I could use the following filters, but it doesn't seem compatible with ALLEXCEPT.

Sales Per KG =
VAR FilteredSales =
FILTER (
'Sheet1', [Attribute] = "Sales Dollars"
)

VAR FilteredWeight =
FILTER (
'Sheet1', [Attribute] = "Weight"
)

RETURN
CALCULATE (
DIVIDE (
CALCULATE (
SUM ( 'Sheet1'[Value] ),
ALLSELECTED ( Sheet1[Attribute]),FilteredSales

),
CALCULATE (
SUM ( 'Sheet1'[Value] ),
ALLSELECTED ( Sheet1[Item Category] ),
FilteredWeight
)
)
)

I hate to ask again, but I've spent all day trying to figure this out. Do you happen to know how I should approach this?

Announcements

#### Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

#### Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

#### Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)