DAX Total by category displayed for single category

Question

I want a total by category for single category. Just like the FruitOnly measure in a picture:

I have a measure:

Total by category = CALCULATE([Sales], ALLEXCEPT('Table','Table'[Category]))

But I want the results only for Fruit, having blanks for all the other categories.

FruitOnly = IF(MAX('Table'[Category])="Fruit", [Total by category], BLANK())

Such a result is cripple because there is no total sum for that measure which should be 45.

Wouldn't there be any simpler way to get the FruitOnly results? Just like the measure:

Fruits raw sales = CALCULATE([Sales], 'Table'[Category]="Fruit")

returns the results only for fruits?

And now the bonus question lifting the challenge to Darth Vader level. Would a measure resulting in FruitOnly be possible if there was no Category displayed in table visual, just Product?

My desperate efforts are here in a file to download:

TotalByCategoryForSingleCategory.pbix

StelioK StelioK · Accepted Answer · 2019-08-21T18:11:08

This will do the trick:

FruitOnly =
CALCULATE (
    [Total by category],
    FILTER ( 'Table', 'Table'[Category] = "Fruit" )
)

I tried it out with your .pbix and I got the following:

DAX Total by category displayed for single category

1 Answers