Grand total for subset of customers

[MortenS]

Hi

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!

[MortenS]

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. 

[FlipFlop1]

Here is another version:

RetailSales Total =

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

            RETURN

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

            TotalSales, BLANK())

[sanalytics · Accepted solution]

@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

[tamerj1]

Hi @MortenS 

Please try

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