Sum, Average or last Month Accumulation - MEASURE Power Bi/Query

[zapppsr]

Hi guys:

This is a matter that comes and go but eventually haunts me and I need to face it.

I need to create a measure that takes in consideration the type of accumulation and give different totals: Sum, Average or Last month value.

The image below ilustrates the problem. I managed to do it but not in a professional and clean way.

Here I'm sure there are some very capable people that can help me.

Thanks in advance.

 

[zapppsr]

Here is the link for the Workbook: Link

The workbook Plan2 has 3 pivot tables. Try to add more than one product to the filter and you will know what I mean...

 

[anvg]

Hi, zapppsr!
Alright.
Let's select all product of page filter for your top pivot table. What does a pivot C4-cell see?
Let's switch to a Power Pivot window and set a filter of NM column to 1 only.
Look at a picture below.

It is a filter content of Plan1 table for that C4-cell. We can see that Accumulation column contains next values "Sum", "Average" and "Last".
Which a measure does have to be computed in this case? Maybe do you want to see three those measure separeted by any delimiter?
Regards, Andrey

 

[zapppsr]

Here is a link to a Google Presentation with step-by-step simulation of the problem.

 

[zapppsr]

OMG, I think I'm crazy!! You were right all along!! (Genius)!!

Once I put the products in line, and months as columns, as it was intended to be since the beginning, it worked!! (See Slide 4 - link)
I didn't try it before because using the method with 3 auxiliary measures didn't work...

But in your way, with only one measure not only it works but is much cleaner, simpler and professional.

I knew I could count on you!!! Thank you very much.

 

[anvg]

I'm glad your success! Good luck!