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

[Anonymous]

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 can see, I the unpivoted columns are the 2 measurement amount columns 1) Detail_NET_AMOUNT, and 2) Detail_RATE_AMOUNT, my lowest granularity key column DETAIL_LINE_ITEM_KEY, and then my lookup table key columns 1) DIM_KEY_DEPARTMENT, 2) DIM_KEY_CONSULTING_COMPANY, and 3) DIM_KEY_CONSULTANT_NAME. Because the Attribute Slicer works with unpivoted columns and ultimately repeats measurements, doing a simple SUMX will not work. For Example: 

Total Costs From AttributeTable = SUMX('Attribute Slicer', 'Attribute Slicer'[Detail_NET_AMOUNT_AMOUNT])

The correct total as seen in the FactTable would be 104,762.

Thus, I went ahead and created a GroupBy using the Attribute Slicer table and took the Max from each group. This should work except the Grand Total is coming out 4,000 less.

I went through and tried to do the IFHASONEVALUE formula, except with a groupby, it didn't work. Any help at all? PBIX file posted below. 

Download pbix here

Link: 

https://drive.google.com/drive/folders/1eqSE17aJOG2PwTZ3sIRu4mcRCfJv7zjt?usp=sharing

[sturlaws · Accepted solution]

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 · Accepted solution]

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]

I can't really see what you are trying to achieve with this attribute table, but in order to do what you are trying to do, write your measure like this

Total Costs From AttributeTable =
IF (
    HASONEVALUE ( 'Attribute Slicer'[Attribute] )
        || HASONEVALUE ( 'Attribute Slicer'[Value] );
    SUM ( 'Attribute Slicer'[Detail_NET_AMOUNT_AMOUNT] );
    CALCULATE (
        SUM ( 'Attribute Slicer'[Detail_NET_AMOUNT_AMOUNT] );
        FILTER (
            'Attribute Slicer';
            'Attribute Slicer'[Attribute] = "Consulting_company"
        )
    )
)

In the 'Attribute slicer'-table, the values of [Detail_net_amount_amount], is, as you say repeated. As long as there is a filter context with a single value for 'Attribute Slicer'[Attribute] or 'Attribute Slicer'[Value], SUM('Attribute Slicer'[Detail_NET_AMOUNT_AMOUNT]) will return a non-repeated value. If neither fields have a single value in the current filter context, repeated values will be returned. In this case the measure above returns the values for an arbitrary chosen value of 'Attribute Slicer'[Attribute] in order to return a non-repeated value.

[Anonymous]

@sturlaws I appreciate this. I'm going to respond to this a little later today. I'm putting together the dummy dataset and data model so that it give a fuller picture of the end goal. 

[Anonymous]

@sturlaws The end goal would be to have a dynamic attribute slicer and a dynamic measurement slicer to toggle between and then be able to drill down with dropdown slicer menus like I have in the pic below. The original formula you presented works, but it singles out CONSULTING_COMPANY within the filter formula. I dont think it will be dynamic filter when toggling between attributes and slicers. What are your thoughts? Does that make sense?

Download link below or click here

https://drive.google.com/open?id=1BnAotut8HbK6m-EROAQtRNMSNzyg_G2Y

[sturlaws · Accepted solution]

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] )
        )
    )
)

[Anonymous]

OMG. I'm going to try this later this afternoon. Would this formula be applicable to an average?

[sturlaws · Accepted solution]

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

[Anonymous]

@sturlaws You're a God amongst people. Average should just skip over blank values, and apologies I wasn't clearer. It was the average of the Detail_RATE_AMOUNT. That's the hourly rate that is being charged for that line item. so wanted to be able to calculate an average hourly based upon attribute. In the picture below Average Total Costs is your formula I just changed it below. Average Rate is a simple AvERAGEX Function which you can see in the pic. But is my simple AVERAGEX formula too simple?

Average Total Costs = 
AVERAGEX (
    CALCULATETABLE (
        DISTINCT ( 'Attribute Slicer'[Value] ),
        ALLEXCEPT ( 'Attribute Slicer', 'Attribute Slicer'[Attribute] )
    ),
    CALCULATE(
        SUM ( 'Attribute Slicer'[Detail_RATE_AMOUNT] )    
    )
)

[sturlaws · Accepted solution]

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

[Anonymous]

@sturlaws I appreciate it. This has been most helpful. Really really appreciate it.