cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jazking123 Frequent Visitor
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.

 

Screenshot.jpg

 

 

Screenshot.jpg

 

Links to the report:

Sample Data

 

PowerBI report

 

 

Thank you if anyone is able to help us!

 

Regards,

 

Gareth.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
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.
3-1.PNG

 

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

View solution in original post

4 REPLIES 4
Highlighted
jazking123 Frequent Visitor
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.

 

Screenshot.jpg

 

 

Screenshot.jpg

 

Links to the report:

Sample Data

 

PowerBI report

 

 

Thank you if anyone is able to help us!

 

Regards,

 

Gareth.

Community Support Team
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.
3-1.PNG

 

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

View solution in original post

jazking123 Frequent Visitor
Frequent Visitor

Re: Calculated measure impacting Waterfall Chart

Thank you so much!

 

The solution works well. You have saved our sanity.

jazking123 Frequent Visitor
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? 

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 323 members 3,044 guests
Please welcome our newest community members: