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



## zapppsr (Apr 20, 2016)

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.


----------



## theBardd (Apr 20, 2016)

Setup 3 measures for Total, Average and Max values, and then something like this


```
=IF(VALUES(Sales[Accumulation])="Sum", [TotalValue],
 IF(VALUES(Sales[Accumulation])="Average", [AverageValue],
 IF(VALUES(Sales[Accumulation])="Last",[MaxValue]
 )))
```


----------



## zapppsr (Apr 20, 2016)

Hi *theBardd*, thanks for replying. That approach was the one I used. I had some trouble and got some messages saying that one type of value was expected and returned more...
Anyway, I will give it one more try... thanks!


----------



## zapppsr (Apr 20, 2016)

*One table with multiple values was provided where a unique value was expected.* 

That is the error I get when I try to use the total field in a pivot table.
I read about it and this approach creates a conflict because in the accumlation field there are more than one type of value...

That is what I'm trying to avoid and looking for a cleaner way to do it...


----------



## zapppsr (Apr 20, 2016)

Note: Even with that #ERROR in the measure shown above, if I use the Total Measure in a pivot table filtered by only one product, it works. But the moment I choose another product togehter, it gives me the "*One table with multiple values was provided where a unique value was expected.* " error.

Since I need all the products shown at the same time, we have a problem.


----------



## theBardd (Apr 20, 2016)

It worked on my, albeit simple, test. There should be no contextual conflict t because you are using VALUES to get a distinct list within the filter context (which is created by the product in the row column). It will show an error in Power Pivot, because there is no product filter applying there, but should be okay in the pivot table.

I notice that you are using semi-colon delimiters, I know that is the situation with non-English Excel but does it also apply to DAX?

Any chance you can post the workbook somewhere?

Oh and BTW, what are you using for the Last measure that gives you 380?


----------



## zapppsr (Apr 20, 2016)

Hi, thanks again for replying.

1) semi-colons is Brazilian Portuguese syntax.

2) Measure for 380:

Last:=CALCULATE (
SUM (Plan1[Value] ); 
FILTER (
ALL ( Plan1[NM]);
Plan1[NM] = MAX ( Plan1[NM] )
)
)

It results the values for March (3, and max value for NM) 100+100+180

3) 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...

I'm thinking of a workaround creating 3 queries one for each different accumulation and merge them together, but this doesn't seem right.


----------



## anvg (Apr 21, 2016)

Hi, zapppsr!
I am not a guru. Matt Allington is a guru here  
I do not understand your pivot table strucuture. Maybe it is like that what you need

```
IF(HASONEVALUE(Plan1[Acumulation]),
    SWITCH(TRUE(),
        VALUES(Plan1[Acumulation])="Sum",SUM(Plan1[Value]),
        VALUES(Plan1[Acumulation])="Average",AVERAGE('Plan1'[Value]),
        VALUES('Plan1'[Acumulation])="Last",CALCULATE(SUM('Plan1'[Value]),FILTER('Plan1','Plan1'[NM]=MAX('Plan1'[NM])))
    )
    , BLANK()
)
```
Regards, Andrey.


----------



## zapppsr (Apr 21, 2016)

The code you provided handled the error fine, but now, when I select more than one accumulation type on the filter, instead of the error, all fields go blank.

It has something to do with the values function, I guess, because it need only one tipe of value...

Thanks for trying to help!


----------



## anvg (Apr 21, 2016)

Could you upload your manually created pivot table  to anywhere? I do not understand what it has to be in it when two accumulation types is performed for a single pivot cell. Other words, for single pivot cell we have "sum" and "average" accumulation types what result do you want see?


----------



## zapppsr (Apr 20, 2016)

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 (Apr 22, 2016)

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 (Apr 23, 2016)

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 (Apr 24, 2016)

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


----------



## zapppsr (Apr 24, 2016)

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 (Apr 24, 2016)

I'm glad your success! Good luck!


----------

