Divide total of a column by unique value depending on group

[Velvetine]

Hi guys,

Im quite new here really appreciate your help.

I have a Orders table comprise of Sales, Outlets, and Outlet_Size columns. I'm trying to calculate the rate, which is something like this DIVIDE(SUM(Orders[Sales],Sales[Outlet_Size]). But how do I write a query that can divide a total of all columns, to one single value depending on which outlet ?

Appreciate your help !

Thank you

[selimovd]

Hey @Velvetine ,

that is possible by modifying the filter context for the total-part. This you can do with the CALCULATE function in combination with the ALL function.

This should do it:

Percentage =
-- you can get the total by removing all filter context
-- with the ALL function. The result you can save in a variable
VAR vTotal =
    CALCULATE(
        SUM( Orders[Sales] ),
        ALL( Orders )
    )
RETURN
-- Then you divide the value of the current filter context by the total
    DIVIDE(
        SUM( Orders[Sales] ),
        vTotal
    )

If you don't like to work with variables (what I would recommend to get used to it), you could also put everything in one statement:

Percentage =
DIVIDE(
    SUM( Orders[Sales] ),
    CALCULATE(
        SUM( Orders[Sales] ),
        ALL( Orders )
    )
)

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic