Forum Discussion
How to divide specific cells by values from another column
Karolina_95
since i dont know you dataset and i assume as per my understanding providing you below approaches.
Ratio =
VAR TotalRevenue = [Total Revenue] // Replace [Total Revenue] with the name of your existing measure for total revenue
VAR TotalResourceValue =
CALCULATE(
SUM(Resources[Value]),
ALL('YourTable'), // Remove any context filter on 'YourTable'
ALL('Resources') // Remove any context filter on 'Resources'
)
RETURN
DIVIDE(TotalRevenue, TotalResourceValue)with month
Ratio =
VAR RevenueTable =
SUMMARIZE(
'YourTable',
'YourTable'[Region],
'YourTable'[Month],
"Total Revenue", [Total Revenue] // Replace [Total Revenue] with the name of your existing measure
)
RETURN
ADDCOLUMNS(
RevenueTable,
"Total Resource Value",
CALCULATE(
SUM(Resources[Value]),
FILTER(
Resources,
Resources[Region] = EARLIER('YourTable'[Region]) &&
Resources[Month] = EARLIER('YourTable'[Month])
)
),
"Ratio",
DIVIDE([Total Revenue], [Total Resource Value])
)
whole year
Ratio =
VAR TotalRevenue = [Total Revenue] // Replace [Total Revenue] with the name of your existing measure for total revenue
VAR TotalResourceValue =
CALCULATE(
SUM(Resources[Value]),
ALL('YourTable') // Remove any context filter on 'YourTable'
)
RETURN
DIVIDE(TotalRevenue, TotalResourceValue)
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!
Super UserKarolina_95 i would suggest below approach
Ratio =
VAR TotalValue = [YourMeasure] // Replace [YourMeasure] with the name of your existing measure
VAR ResourceValue = CALCULATE(SUM(Resources[Value]), FILTER(Resources, Resources[Month] = SELECTEDVALUE('Calendar'[Month])))
RETURN
DIVIDE(TotalValue, ResourceValue)
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!
Hi, thank you for the reply.
The issue here is that I don't want to specifying for which month and which resource it should do it, but rather want to have it calculate it for a whole year so the question is how do i litterally get it to divide 500/2,5 in Jan for America, 690/5 in Jan for Asia etc?
Also the tables are separated and I tried creating a relationship basis the region however it is many to many relationship
- johnbasha33
Karolina_95
since i dont know you dataset and i assume as per my understanding providing you below approaches.
Ratio =
VAR TotalRevenue = [Total Revenue] // Replace [Total Revenue] with the name of your existing measure for total revenue
VAR TotalResourceValue =
CALCULATE(
SUM(Resources[Value]),
ALL('YourTable'), // Remove any context filter on 'YourTable'
ALL('Resources') // Remove any context filter on 'Resources'
)
RETURN
DIVIDE(TotalRevenue, TotalResourceValue)with month
Ratio =
VAR RevenueTable =
SUMMARIZE(
'YourTable',
'YourTable'[Region],
'YourTable'[Month],
"Total Revenue", [Total Revenue] // Replace [Total Revenue] with the name of your existing measure
)
RETURN
ADDCOLUMNS(
RevenueTable,
"Total Resource Value",
CALCULATE(
SUM(Resources[Value]),
FILTER(
Resources,
Resources[Region] = EARLIER('YourTable'[Region]) &&
Resources[Month] = EARLIER('YourTable'[Month])
)
),
"Ratio",
DIVIDE([Total Revenue], [Total Resource Value])
)
whole year
Ratio =
VAR TotalRevenue = [Total Revenue] // Replace [Total Revenue] with the name of your existing measure for total revenue
VAR TotalResourceValue =
CALCULATE(
SUM(Resources[Value]),
ALL('YourTable') // Remove any context filter on 'YourTable'
)
RETURN
DIVIDE(TotalRevenue, TotalResourceValue)
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!