topic Re: Grand total for subset of customers in DAX Commands and Tips

Grand total for subset of customers

MortenS — Fri, 06 Sep 2024 19:00:25 GMT

I have a DAX problem that I have been trying to solve for some time. But for some reason, I cannot seem to wrap my head around it.

I have presented a simple version of the data model. Date table is intentionally left out as it has no relevance here.

The problem: I want to write a measure that shows the grand total of all sales (8.649.004) but only shown for the customers with the customer type "Retail". For the last 4 customers the measure should produce a blank.

Help on this challenge would be highly appriciated!

Re: Grand total for subset of customers

tamerj1 — Fri, 06 Sep 2024 20:22:11 GMT

Hi MortenS

Please try

Total Sales - Display for Retail =
IF (
"Retail" IN VALUES ( Customer[Customer Type] ),
CALCULATE ( [Total Sales], ALLSELECTED ( Customer[Customer Nsme] ) )
)

Re: Grand total for subset of customers

sanalytics — Sat, 07 Sep 2024 11:48:19 GMT

MortenS

You can achieve youe solution by below DAX measure as well.

VAR _1 = CALCULATE( [Total Sales], ALLSELECTED( Customers ) ) VAR _Result = IF( MAX( Customers[Customer Type] ) = "Retail", _1, IF( NOT ISFILTERED( Customers[Customer Name] ), _1 ) ) RETURN _Result

Below is the screenshot for your reference

Please find the below attachment of the pbix file

https://we.tl/t-lUiRkJ75hX

Hope it helps

Regards

sanalytics

If it is your solution then please like and accept it as solution

Re: Grand total for subset of customers

FlipFlop1 — Sun, 08 Sep 2024 15:22:28 GMT

Here is another version:

RetailSales Total =

VAR TotalSales = CALCULATE([TotalSales], REMOVEFILTERS())

RETURN

IF(SELECTEDVALUE(Customer[Type])="Retail",

TotalSales, BLANK())

Re: Grand total for subset of customers

MortenS — Sun, 08 Sep 2024 16:30:26 GMT

Hi all,

Thank you for your different solutions - most of them work or were easy to adapt to get to work.

Would it also be possible to get to achieve the same solution with a measure with a nested Calculate to replace the if-statement? So an outer Calculate to set the filter context of e.g. "Retail" and the inner to sum the total sales? I have tried a number of ways to get this approach to work, but I have not been able to write a useable measure.

Would appreciate your thougts on this idea.