Measure aggregated based on dimensions

[ovinasca]

Hi all!

I need to create a measure that aggregates based on dimensions, I mean, when its aggregated by the Time Dimension I need to SUM the values but when its aggregated by the Department Dimension I need to AVERAGE the values.

Thanks in advanced.

Regards

[v-jiascu-msft]

Hi @ovinasca,

Can you share a sample please? In other words, can we take the non-total values as the original data?

Best Regards,

Dale

[ovinasca]

Yes it is!

The non total values are the original data and the total values should be the aggregation output depending on the dimension. 

The picture shows what would be the final result

Thanks!!!

[Greg_Deckler]

So, does that represent source data, expected output, neither, both?

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[ovinasca]

Sorry and thanks for the post you suggested!!!

The pìcture shows what should be the final result. We have a measure called FTE's with two dimensions, WEEK and DEPARTMENT.

I want the values of FTE's to be aggregated as SUM when working with WEEK (Time) Dimension and I want them to be aggregated as AVERAGE when working with DEPARTMENT Dimension.

Hope if helps.

Thanks!!

Regards

[ovinasca]

@Greg_Deckler

@v-jiascu-msft

Sorry I forgot both mentions.

THANKS!!!

[Greg_Deckler]

@ovinasca- What would be very helpful is if you could share the source data that results in the totals that you shared.

[ovinasca]

Hi again @Greg_Deckler!!!

That's only a "hand-made" example built in excel.... there's no sourde data at all. I have used it to show what i'm trying to do, but i don't know how....

Thank you very much!

Regards

[Greg_Deckler]

OK, so what would that look like if it was aggregated by the "time dimension"?

[ovinasca]

That's TOTAL(2) in the picture, and if you aggregate by department dimension you whould obtain TOTAL (1)

[ovinasca]

Hope it helps.... 

Maybe it's not possible....

Thanks!

[Greg_Deckler]

Is there a reason why you cannot just have 2 measures, one for SUM and one for AVERAGE?

[ovinasca]

Well, I could try to use a work-around presentation for the data.

But anyway, using two different measures I will have the same problem working with matrix:

The red cells are wrong.....

[Vvelarde · Accepted solution]

@ovinasca

Hi, try with this Measure in Values Section

Measure =
IF (
    HASONEVALUE ( Table1[TIME] );
    SUM ( Table1[FTE's] );
    AVERAGE ( Table1[FTE's] )
)

Regards

Victor

Lima - Peru

[Anonymous]

Solution is great and solves the initial problem. But can you help on the Total Sum cell as well? Current solution calculates the AVERAGE on the full table which returns 5 which doesn't make much sense. What is the solution to make the AVERAGE of total SUMs instead of AVERAGE of whole table at lowest level? The correct result should be 15

[ovinasca]

Thanks @Vvelarde I will try tomorrow, I think i will work for that specific example, but if I have other dimension?

Not sure....

I will tell you !!!!

THANKS!

[ovinasca]

Hi @Vvelarde!! 

Works only for the suggested example, I mean, working only with two dimensions but works for me!

So thanks!!