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.

 

[theBardd]

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]

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]

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]

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]

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]

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]

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]

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]

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?