Average of columns of related and main tables ignoring pivot rows

[Anonymous]

Hi

I have two tables which are related to each other. Have created a pivot data model. I'm trying to get the average of one of the columns from the related table and the current table in a single pivot. The average should be based on a slicer rather than individual rows of the pivot table. I have tried to capture my thoughts in the Sample Excel.

My aim is to get all the columns in green in a single pivot which should seamlessly work with the provided slicer.

Please help me.

Thanks

Guru

[selimovd · Accepted solution]

Hey @Anonymous ,

I think I found a solution.

First of all you have to use the slicer from the dimensional table, so from Range[CAT_PRIMARY_KEY] and not from 'Range 1'[CAT_FOREIGN_KEY] as the table Range is filtering Range 1 and not the other way around.

Once you did that these are the measures:

Average Range Value :=
CALCULATE(
    AVERAGE( Range[Value] );
    ALLSELECTED( Range[CAT_PRIMARY_KEY] )
)

And the spread:

Average Spread :=
CALCULATE(
    AVERAGE( 'Range 1'[Spread] );
    ALLSELECTED( Range[CAT_PRIMARY_KEY] );
    ALL( 'Range 1'[Part] )
)

Then Measure1 is just an easy calculation:

Measure1 := SUM( 'Range 1'[Spread] ) * [Average Range Value]

As well as Measure2:

Measure2 := SUM( 'Range 1'[Spread] ) * [Average Spread]

The result looks then identical:

My solution you will find here:

https://www.swisstransfer.com/d/43aa970d-2670-462b-bb6d-bf4fed3a85fd

I hope I could help a Guru 😉

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[selimovd · Accepted solution]

Hey @Anonymous ,

I think I found a solution.

First of all you have to use the slicer from the dimensional table, so from Range[CAT_PRIMARY_KEY] and not from 'Range 1'[CAT_FOREIGN_KEY] as the table Range is filtering Range 1 and not the other way around.

Once you did that these are the measures:

Average Range Value :=
CALCULATE(
    AVERAGE( Range[Value] );
    ALLSELECTED( Range[CAT_PRIMARY_KEY] )
)

And the spread:

Average Spread :=
CALCULATE(
    AVERAGE( 'Range 1'[Spread] );
    ALLSELECTED( Range[CAT_PRIMARY_KEY] );
    ALL( 'Range 1'[Part] )
)

Then Measure1 is just an easy calculation:

Measure1 := SUM( 'Range 1'[Spread] ) * [Average Range Value]

As well as Measure2:

Measure2 := SUM( 'Range 1'[Spread] ) * [Average Spread]

The result looks then identical:

My solution you will find here:

https://www.swisstransfer.com/d/43aa970d-2670-462b-bb6d-bf4fed3a85fd

I hope I could help a Guru 😉

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

Thanks a lot @selimovd. You don't know how much you have helped me. I've been struggling with this one from past 5hrs. Average value worked very well but the average spread should also be similar to average value i.e rather than broken down by the category it should be the average spread of all selected categories.

By the way I'm just a namesake Guru. You are the real 'Guru'.

[selimovd · Accepted solution]

Hey @Anonymous ,

I'm happy I could help you and thanks for the compliment, haha 😊

OK, got it, replace the [Average Spread] measure with the following one:

Average Spread :=
CALCULATE(
    AVERAGE( 'Range 1'[Spread] );
    ALLSELECTED( Range[CAT_PRIMARY_KEY] );
    ALL(
        'Range 1'[CAT_FOREIGN_KEY];
        'Range 1'[Part]
    )
)

That should work.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

But there is one issue here that is the slicer is based on PRIMARY KEY whereas pivot table is based on FOREIGN KEY. So the selection of items in the slicer is not changing the categories in the pivot, though the calculations are working correctly. If PK is used for pivot table then part names are not according to the FK table. Is there a way around for this?

[CNENFRNL]

Fundamental calculations with DAX

[Anonymous]

Thank you @CNENFRNL for the reply but my expectation is to use the average of column in the pivot measure which I was unable to since the value gets broken down according to the rows of the pivots.