Help Using Subtotals in Division

Anything related to PowerPivot and DAX Formuale
cm1sutton
Posts: 3
Joined: Wed Dec 11, 2019 8:29 am

Help Using Subtotals in Division

Postby cm1sutton » Wed Dec 11, 2019 8:39 am

I am not 100% sure how to explain this which is why my searches are coming up empty but I am sure this is fairly simple.
I have a list of GLCodes that comprise of transaction amounts. I need to group the GL's into several group and subtotal them which I have done. Then I need to take one of the groups (Netsales) and use that total as a denominator for each group to find the margin percentage. I have been able to complete that step at the subtotal level. However, I would like to also use that Netsales total to find the margin percent of each gl in the group. Any guidance is appreciated.

[attachment=0]Capture.JPG[/attachment]
Attachments
Capture.JPG
Capture.JPG (26.8 KiB) Viewed 132 times

Bernard Heymans
Posts: 33
Joined: Wed Feb 28, 2018 12:18 am

Re: Help Using Subtotals in Division

Postby Bernard Heymans » Thu Dec 12, 2019 2:30 am

Hi,

several ways are possible.


= sum(NS) //this is what I will divide
/ // here I will divide
calculate( sum(NS) , // take the NS
ALL( 'mydatatable') , // remove all filters
'mydatatable[GlAccountnr]="48750" ) // restrict the table to keep only rows with glaccount= 48750



What does I do:
I divisde de sum(NS) by the sum of NS


=> so you have to learn filter...

why not a book?
https://www.amazon.fr/Power-Pivot-Bi-Ex ... 763&sr=8-1
Attachments
filter exemple.xlsx
(110.93 KiB) Downloaded 5 times

cm1sutton
Posts: 3
Joined: Wed Dec 11, 2019 8:29 am

Re: Help Using Subtotals in Division

Postby cm1sutton » Fri Dec 13, 2019 8:15 am

Thank you for your reply.


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 0 guests

cron