Conditional formatting on "items with no data"

[Sergiy]

Hi there,

Here are links to the test sample I made:

Total.bbix

Total.xlsx

There are two tables: Shops and Sales.

 

Some of the shops have corresponding records in Sales table, some shops do not.

I need to make a Table visual displaying Sum([Price]) for every shop in Shops table. Shops that don't have sales also have to be displayed.

Category column of Shop table has to be colored by Conditional Formatting. That coloring is my main aim.

I tried some of approaches but all of them are not perfect.

1. 

The Table visual displays: Shops[Shop] column, Shop[Category] column (don't summarize option is On), SumMeasure

As you see, in that case Conditional Formatting works incorrect. Not all categories are colored as expected.

2.

The Table visual displays: Shops[Shop] column, Shop[Category] column ('First' option is On), SumMeasure

Here all categories are colored as expected, but as a drawback a letter "A" is displayed underneath, which is not good.

3.

The Table visual displays: Shops[Shop] column, CategoryMeasure column, SumMeasure

CategoryMeasure = IF(COUNTA(Shops[ShopId])<>1,BLANK(),MIN(Shops[Category])) 

That is the most desirable variant: all categories are colored as expected, there are no letters underneath the visual.

To display all shops "Show items with no data" option is set to On for all Table visuals.

Now I apply a filter using a slicer and select one of the shops. As you see, second and third variants have letter "A" as a total, which is not good.

So, I am stumped. I'd like the categories to get colored and unwanted totals are not to be present.

Could you please advise on what could be done? 

[Seward12533]

@Sergiy for soluiton 3 try using IFHASONEFILTER rather than the IF(COUNTA( test in your blocking measure. 

CategoryMeasure = IF(HASONEFILTER(Shops[ShopId]),MIN(Shops[Category])) 

[Sergiy]

@Seward12533

Thank you for your reply.

I guessed you ment using Shops[Shop], not Shops[ShopId]:

Otherwise the measure won't show anything at all.

But even if we change the measure like this:

CategoryMeasure = IF(HASONEFILTER(Shops[Shop]),MIN(Shops[Category])) 

The result will be exactly the same as when I used "COUNTA" function: if one shop is selected we still have a total row

[Seward12533]

Sorry didn’t notice that as part of my testing I changed the relationship between the tables from crossfiltwr in BOTh directions to SINGLE.