Forum Discussion
Calculating ratio from column consistent when drilling through
Hi,
Just started a couple of months ago on Power BI.
I'm trying to calculate spend/revenue against a monthly spend and revenue data. So dividing the column I would easily get this ratio. But when going through the "Quarter" and "Year" hierarchy, the result is the sum of the 3 months ratio or the average result (based on summarization)
See table above, I would get a result of $0.29 for the spend/revenue average of the 3 month, but the real value would be 3021/111611=$0.03.
Hi,
Remove the Spend/revenue column from your raw data table. Create a Calendar Table with calculated column formulas for Year, Quarter, Month name and Month number. Sort the Month name by the Month number. Create a relationship (Many to One and Single) between the date column of your raw data table and the Date column of the Calendar Table. To your visual, drag Year, Quarter and Month name from the Calendar Table. Write these measures:
Total spend = sum(Data[spend])
Total revenue = sum(Data[revenue])
Spend revenue = divide([total spend],[total revenue])
Hope this helps.
3 Replies
- Ashish_Mathur
Super User
Hi,
Remove the Spend/revenue column from your raw data table. Create a Calendar Table with calculated column formulas for Year, Quarter, Month name and Month number. Sort the Month name by the Month number. Create a relationship (Many to One and Single) between the date column of your raw data table and the Date column of the Calendar Table. To your visual, drag Year, Quarter and Month name from the Calendar Table. Write these measures:
Total spend = sum(Data[spend])
Total revenue = sum(Data[revenue])
Spend revenue = divide([total spend],[total revenue])
Hope this helps.
- amitchandak
Mumtazian20 , How are you doing that , can you share the formula
It Should be
Divide(sum(Table[Spend]),sum(Table[Revenue]))
if you need extend monthly Avg - Avg to Avg
then
AverageX(Values(Date[Month Year]), Calculate(Divide(sum(Table[Spend]),sum(Table[Revenue])) ) )
amitchandak I've tried before these formula, and it works for the month value on the table and also on the chart.
But in the chart when going through the hierarchy "Quarters" e.g. it would summarize the 3 months. Hence, for the previous example, I would get value of $0.29 average. But this is not the value I want, the value I want will be the "sum of the spend for the quarter"/"sum of the revenue for the quarter" = 3021/111611=$0.03, and so on.
Instead of direct spend/revenue average, the value needs to be recalculated to sum all month value (spend and revenue) in the quarter then divide.
