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.

     

v-eachen-msft's avatar
v-eachen-msft
Community Support
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

    Thank you so much!

     

    The solution works well. You have saved our sanity.

  • jazking123's avatar
    jazking123
    Frequent Visitor
    6 years ago

    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?