Forum Discussion

Karolina_95's avatar
Karolina_95
Frequent Visitor
2 years ago
Solved

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 reg...
Need Help
  • johnbasha33's avatar
    johnbasha33
    2 years ago

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



Karolina_95's avatar
Karolina_95
Frequent Visitor
2 years ago

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's avatar
johnbasha33
Icon for Super User rankSuper User
2 years ago

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