Two DAX formula questions

[cr731]

I'm just starting to familarize myself with Powerpivot and DAX formulas and I have a feeling it would be ideal for what I'm trying to do; I just can't figure it out. I'm looking for two things:

1. Calculate a % of total within certain groupings. So for instance, my table may look something like this:

Group 1	Group 2	Group 3	Value
Red	Light	A	100
Red	Medium	B	-50
Yellow	Dark	C	400
Orange	Medium	B	500
Red	Light	C	200

<tbody>
</tbody>

So what I need this formula to do is calculate the % of total based on the pairing of Group 2 and 3 but within Group 1. For instance,

Red > Light > A should be 100 / (100 + (-50) + 200) = 40%
Red > Medium > B should be - 50 / (100 + (-50) + 200) = -20%

There is one additional step and that is that I need to calculate based on absolute values. So my examples above would turn into:

Red > Light > A should be 100 / (100 + 50 + 200) = 29%
Red > Medium > B should be -50 / (100 + 50 + 200) = 14%

So that is formula # 1 I need.

The second one that I need is to calculate the average but also be able to calculate a trimmed average. It needs to be based on the combination of all 3 groupings (my source data will have many more rows). But essentially I would need it to calculate the average of all rows where 1 = Red, 2 = Medium, and 3 = B ... and to be able to average only if the values are within a set range. Otherwise, just ignore them.

I could do all of this in an Excel table but is this the sort of thing Powerpivot would help with?

 

[mozartiano]

Hello,

I posted a question about % of total last week and looks like it is resolved :D

You will create 3 calculated fields:

ROW TOTAL := SUM(VALUE) *** here you will say for each row to get the sum based on its current row. For instance, if you choose to put GROUP1 in the row, it will calculate the sum for all values related to the element of the current row of the pivot table (or power view), for instance, it will show the total for RED.

TABLE TOTAL := CALCULATE(SUM([VALUE]);ALLEXCEPT(TABLE;[GROUP2];[GROUP3]) *** this formula will calculate the sum of "VALUES" taking into consideration the Values for GROUP2 and GROUP3, which you should position in the columns of your pivot table.

Then, you create another field to divide the first by the second.

TOTAL ALL := DIVIDE(ROW TOTAL;TABLE TOTAL)

Let us know if it works and put all three fields in your pivot table to try to understand what is going on. Also, try to study the ALL, ALLEXCEPT and ALLSECTED functions to tune it.

Thanks

 

[mozartiano]

The average thing can be done using the AVERAGEX function. It accepts to arguments, the first one being a table and the second the column that you want to compute the average of.

I suggest using the FILTERS function within it, something like Averagex(Filters(value > 100 && value <=500);value)

Again, as your demand is very specific, you should study the function and exercise it until you find your solution. Then let us know your results :D

Link to the average function AVERAGEX Function

Thanks,

Alex

 

[cr731]

TABLE TOTAL := CALCULATE(SUM([VALUE]);ALLEXCEPT(TABLE;[GROUP2];[GROUP3]) *** this formula will calculate the sum of "VALUES" taking into consideration the Values for GROUP2 and GROUP3, which you should position in the columns of your pivot table.

I'm running into problems with this. So my source data table has about 10 more columns than I put in the example, because the rest are not relevant for this calaculation. But essentially I need something like: SUMIFS([Value],[Group1] = this row Group 1 value, [Group2] = this row Group 2 value.

I tried CALCULATE(SUM[VALUE],ALL[GROUP1]) to get the Group1 totals but I get an error that "The value for Group1 cannot be determined." What am I doing wrong?

 

[mozartiano]

Hello,

well, the ALL function means ignore ALL filters. ALLEXCEPT means ignore all filters except the ones that you specify here.

You can try CALCULATE(SUM[VALUE],ALL[TABLENAME]) that will always define this field as the TOTAL of VALUE regardless of the context and it will be used as denominator in your expression. I am curious with the result of this test, please update us on this .

 

[scottsen]

I didn't super read this thread, but on the error:

CALCULATE(SUM[VALUE],ALL[GROUP1])

Correct syntax:
=CALCULATE(SUM(TableName[ColumnName]), ALL(TableName))
or maybe
=CALCULATE(SUM(TableName[ColumnName]), ALL(TableName[SomeColumnName]))

 

[scottsen]

I think mozartiano did pretty good. I would try...

Total - All Group 1 := CALCULATE([Total], ALL(Table1[Group 1]))
% of Group 1 := DIVIDE([Total], [Total - All Group 1])

 

[paulkltam]

While I can workout the % to the column total. If I just want to show those greater than 10%, the rest grouped to "others". How's the Dax?