Forum Discussion

jazking123's avatar
jazking123
Frequent Visitor
6 years ago
Solved

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...
Need Help
  • v-eachen-msft's avatar
    v-eachen-msft
    6 years ago

    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.

     

jazking123's avatar
jazking123
Frequent Visitor
6 years ago

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.

 

 

 

 

Links to the report:

Sample Data

 

PowerBI report

 

 

Thank you if anyone is able to help us!

 

Regards,

 

Gareth.