# Two DAX formula questions

#### cr731

##### Well-known Member
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?

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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

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

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?

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 .

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

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

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])

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?

Replies
0
Views
453
Replies
0
Views
195
Replies
0
Views
350
Replies
2
Views
558
Replies
1
Views
345

### Forum statistics

1,196,298
Messages
6,014,532
Members
441,826
Latest member
roudarreza ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back