cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
beingandbrian Regular Visitor
Regular Visitor

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 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. Attribute Value.PNGBecause 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])image.png

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

 

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.image.png

 

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

3 ACCEPTED SOLUTIONS

Accepted Solutions
sturlaws New Contributor
New Contributor

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

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

View solution in original post

sturlaws New Contributor
New Contributor

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

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

View solution in original post

sturlaws New Contributor
New Contributor

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

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

View solution in original post

9 REPLIES 9
sturlaws New Contributor
New Contributor

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

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.

 

 

beingandbrian Regular Visitor
Regular Visitor

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

@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. 

beingandbrian Regular Visitor
Regular Visitor

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

@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?image.png

 

Download link below or click here

 

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

sturlaws New Contributor
New Contributor

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

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

View solution in original post

beingandbrian Regular Visitor
Regular Visitor

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

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

sturlaws New Contributor
New Contributor

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

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

View solution in original post

Highlighted
beingandbrian Regular Visitor
Regular Visitor

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

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

image.png

 

sturlaws New Contributor
New Contributor

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

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

View solution in original post

beingandbrian Regular Visitor
Regular Visitor

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

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

 

Helpful resources

Announcements
New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,524)