Divide total of a column by unique value depending on group

Velvetine — Fri, 08 Oct 2021 08:50:51 GMT

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

Re: Divide total of a column by unique value depending on group

selimovd — Fri, 08 Oct 2021 12:44:42 GMT

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

topic Re: Divide total of a column by unique value depending on group in DAX Commands and Tips

Divide total of a column by unique value depending on group

Re: Divide total of a column by unique value depending on group