Make calculation per line then summarize by line item

[naveen73]

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

[daxer-almighty]

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.