Forum Discussion
How to divide specific cells by values from another column
Hi, I need to replicate the calculations done in Excel where a sum of different filtered values per month gets divided by the available resources per month. All of that is also then split between regions.
I have created a measure that filters and sums all the data I need dividing but I am struggling how to get it to divide it by correct number of available resources.
This is the output from the measure. The top are the months
| Region | 1 | 2 | 3 | 4 |
| Americas | 500 | 400 | 420 | 380 |
| Asia | 690 | 600 | 630 | 600 |
| Europe | 900 | 790 | 770 | 800 |
The resources sample available in an Excel table look like this
| Region | 1 | 2 | 3 | 4 |
| Americas | 2,5 | 2,5 | 3 | 3,5 |
| Asia | 5 | 3 | 5 | 5 |
| Europe | 7 | 7,5 | 8 | 6 |
My data model is connecting the two tables on the region with many to many relationship.
My question is how to get the specific cells divided by the specific value from resource for the specific month?
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 !!
4 Replies
- amitchandak
Super User
Karolina_95 , Assume you measure is M1
You need this new measure
Divide([M1], calculate([M1], removefilter(Table[Region])) )
Percent of Total and Percent of SubTotal https://www.youtube.com/watch?v=6jTildcV2ho
https://www.youtube.com/watch?v=cN8AO3_vmlY&t=24270s - johnbasha33
Karolina_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 !!
