Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Working with tidy/long data Grand Total Incorrect When working with a GroupBy in a Variable.

Hey all.    The goal here is to create a dynamic Attribute Slicer and Measure Slicer. To do this, I need to unpivot my Fact Table which is at the Detail Line Item Level.  In the picture below you c...
  • sturlaws's avatar
    sturlaws
    6 years ago

    You can change the code to this:

    Total Costs =
    IF (
        HASONEVALUE ( 'Attribute Slicer'[Attribute] );
        SUM ( 'Attribute Slicer'[Detail_NET_AMOUNT_AMOUNT] );
        CALCULATE (
            SUM ( 'Attribute Slicer'[Detail_NET_AMOUNT_AMOUNT] );
            FILTER (
                'Attribute Slicer';
                'Attribute Slicer'[Attribute] = SELECTEDVALUE ( 'Attribute Slicer'[Attribute] )
            )
        )
    )
  • sturlaws's avatar
    sturlaws
    6 years ago

    oops, the [Total cost]-measure was a bit more complicated than necessary. It should be written like this:

    Total Costs = 
        CALCULATE (
            SUM ( 'Attribute Slicer'[Detail_NET_AMOUNT_AMOUNT] );
            FILTER (
                'Attribute Slicer';
                'Attribute Slicer'[Attribute] = SELECTEDVALUE('Attribute Slicer'[Attribute])
            )
        )

    If you want the average across an attribute you could use this code:

    Average Total Costs = 
    AVERAGEX (
        CALCULATETABLE (
            DISTINCT ( 'Attribute Slicer'[value] );
            ALLEXCEPT ( 'Attribute Slicer'; 'Attribute Slicer'[Attribute] )
        );
        CALCULATE(
            SUM ( 'Attribute Slicer'[Detail_NET_AMOUNT_AMOUNT] )    
        )
    )

    But this will depend a bit on how you want to use it. Also think about how to handle blank values; should blank values count as 0 in the average, or should the average just be over the attribute values which have a value

  • sturlaws's avatar
    sturlaws
    6 years ago

    It all depends on what you want to do. As you can see in your screenshot, [Average rate] is changing with each 'Attribute'[Value]. This is because your code now calculates the average rate for each 'Attribute'[Value]. E.g. look at the second line of the table Draft/revise. On this line the filter context is 'Attribute'[Value]="Draft/revise". This means that in this line, with your average calculation, you are calculating the average of rows from your table where 'Attribute'[Value]="Draft/revise". If this is you desired output, your code is perfect.

     

    If you want to compare rates from, say company, to the average of all companies, you will have to use DAX-functions to alter the filter context, like the example code I sent you.

     

    Cheers,
    Sturla