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