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
naveen73
Helper III
Helper III

Make calculation per line then summarize by line item

Hi,

 

I have a two columns

 

  1. Values of a product
    Percentages of discount e.g. 0.25
  2. My goal is to divide column 1 by column 2. and sum this per category such as region, colour etc.
    This is the DAX that I have:

Value := Sum([Value of Product])
Percentage := Sum([Percentage])

 

Then I make the following calculation:

 

Total_value [Value] / [Percentage]

 

Or

 

Divide(Value, Percentage,0)

 

In both cases when I drag and drop it to the pivot table in Excel the following happens:
1. The calculation per line items are correct
2. When collapsing it my let’s region it seems to take the total sum of the column Value and divide by the total sum of the column

 

Percentage rather than divide the value by the percentage of each line and sum the results by region.

What am I doing wrong?

 

Thanks,

Nav

1 REPLY 1
daxer-almighty
Solution Sage
Solution Sage

Create a new column (best in Power Query) where you'll calculate the ratio. Then bring this column into the DAX table and your measure will just sum up the values in the column.

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.

Top Solution Authors