Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dokat
Post Prodigy
Post Prodigy

Sum certain rows then divide with another with DAX formula

Hi,

 

I am trying to do a calculation in matrix table. I'd like to calculate a rate (% of Gross) by summing up certain rows (NS, Contribution,OS) and divide by Gross to calculate % rate. Below is sample data and what i am trying to accomplish.

 

% of Gross           SUM(NS+Contribution+OSG)/Gross

 

I tried numerous formulas but none of them work.

 

Calendar YearCustomerPL LineValues
2019ACME MarketsVolume                                                     1,972,240
2019ACME MarketsGross                                                         697,503
2019ACME MarketsReturns                                                                     -  
2019ACME MarketsDiscounts                                                              3,398
2019ACME MarketsOSG                                                         206,648
2019ACME MarketsNS                                                         206,224
2019ACME MarketsContribution                                                       (109,594)
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@dokat , Try a new measure like

 


Divide(
calculate(sum(Table[Values]), filter(Table, Table[PL Line] in {"NS","Contribution","OSG"})),calculate(sum(Table[Values]), filter(Table, Table[PL Line] in {"Gross"})))

View solution in original post

2 REPLIES 2
dokat
Post Prodigy
Post Prodigy

It worked. Thank you!

amitchandak
Super User
Super User

@dokat , Try a new measure like

 


Divide(
calculate(sum(Table[Values]), filter(Table, Table[PL Line] in {"NS","Contribution","OSG"})),calculate(sum(Table[Values]), filter(Table, Table[PL Line] in {"Gross"})))

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.