Percentage based on parent total.

[alfranco17]

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?

Thanks for your help!
Armando.

 

[Jacob Barnett]

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:

[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

 

[alfranco17]

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

 

[Jacob Barnett]

OK. Makes sense! Try this:

[% 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

 

[alfranco17]

Amazing! Thank you, Jacob.

I have one last question, if you'd be so kind. Why is

ALL(Financial[Description]

different from

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

 

[Jacob Barnett]

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

 

[alfranco17]

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

 

[alfranco17]

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.