# Percentage based on parent total.

#### alfranco17

##### Board Regular
Hi.

I have just discovered Power Pivots. I think DAX can solve an issue I have with regular Pivot tables.

I have to create a report that divides all amounts for a given month between the total income amount for that month. I almost get it with a regular Pivot Table.

http://www.auval.com.mx/images/1Mess.JPG

I can copy those values and format and fix the % with a formula per column.

http://www.auval.com.mx/images/1Need.JPG

Trying to use a Power Pivot, I created a new column called "Income only" in the linked table, with the formula =CALCULATE(SUM(Financial[Amount]),Financial[Type]="Income")

Then tried to get the sum of this new column on all the items on the table filtered by the month in the column with this measure:

=CALCULATE(SUM(Financial[Amount]),Financial[Type]="Income", filter(ALL(Financial),Financial[Month]=[month]))

The issue I have is that ALL removes the filter for all items, and then my filter gets ignored and always gets the total sum (635, instead of either 315 for Jan or 320 for Feb). (Or maybe my real issue is that my logic is just plain wrong.)

Is there a way to include the month name in the calculation, or a formula that does the trick?

Armando.

### Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Armando,

Your problem isn't 100% clear to me but what I think you are trying to do is show the percentage for each description of its type in the same month. If so the good news is that you are almost there!

Its good practice to create a proper measure for anything in the Values section of the pivot, even if it would have happily created an implicit one by you dragging in the column. On this basis I would create a measure to sum the amount column and then use that again in a further measure which calculates the denominator:

Code:
``````[Sum Amount] = SUM(financial[Amount])

[% of Type]= [Sum Amount] / CALCULATE([Sum Amount],ALL(Financial[Description]))``````

Your pivot has introduced 3 filter contexts to your [Sum Amount] measure; Type, Description and Month and what the bottom half of the second measure does is just open out the Description context meaning the formula returns a number for the same type and month but for all descriptions.

Hope this is what you were looking for, if not let me know.
Jacob

Armando,

Your pivot has introduced 3 filter contexts to your [Sum Amount] measure; Type, Description and Month and what the bottom half of the second measure does is just open out the Description context meaning the formula returns a number for the same type and month but for all descriptions.

Jacob

Wow Jacob, that was fast. Thanks.
It certainly helped a lot. Now I can see the percentages on all rows, and know how to calculate the percentage of the "parent" field contributed by the row.

I am missing just one thing: I want to divide all the expense amounts between the total income for that store and month, excluding all the expenses from the total. Just income.

I have uploaded the following picture, that may be easier to follow than my explanation:

http://www.auval.com.mx/images/1Solution.JPG

<P> <IMG SRC="http://www.auval.com.mx/images/1Solution.JPG"><P><http: 1solution.jpg"="" images="" www.auval.com.mx="">
Thanks.
Armando

</http:>

Last edited:
OK. Makes sense! Try this:

Code:
``````[% Split] = [Sum Amount]/CALCULATE([Sum Amount],ALL(Financial[Description]))

[% Split II] = [Sum Amount]/-CALCULATE([Sum Amount],ALL(Financial[Type],financial[Description]),Financial[Type]="Income")

[Final % Split] = IF([Sum Amount]>0, [% Split],[% Split II])``````

Jacob

Amazing! Thank you, Jacob.

I have one last question, if you'd be so kind. Why is
Code:
``ALL(Financial[Description]``

different from

Code:
``ALL(Financial[Type],financial[Description])``

If I include all types, am I not including all descriptions also?

Thanks again for all your help, it is very exciting when I have to work this hard to understand a formula.

Best regards
Armando

Last edited:
Armando,

ALL(Financial[Description]) only opens up the filter context for that column whereas ALL(Financial[Type],financial[Description]) opens up the filter context for both columns referenced. ALL(Financial) opens up the context for the entire table.

Jacob

Thanks! <p> I don't quite get it yet, but I will practice it and make a post when the pivot bulb goes fully on. <p>Best regards.<p>Armando

Got it!

I created a YouTube video with instructions. Thinking about explaining it was the best way to understand it. I thanked you in the notes.

When I realized I can use the second formula, and add ABS, I thought "the pivot bulb is on". Right now I feel like a toddler learning to walk with pivot tables.

Thanks again.
Armando.

Replies
0
Views
162
Replies
1
Views
789
Replies
13
Views
682
Replies
0
Views
94
Replies
0
Views
164

1,203,640
Messages
6,056,493
Members
444,870
Latest member
xuanhoi

### 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