How to divide specific cells by values from another column

[Karolina_95]

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?

[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 !!

[Karolina_95]

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 · Accepted solution]

@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 !!

[amitchandak]

@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