Don't show the row if it has blank against a dimension in column

[likhitha2705]

Hello All,
I'm using Matrix visual,
where Sub Category and Product are in Rows
Country is in Columns
and Sales as my Values.
Assume one of country selection is fixed(Ex : In my scenario it is "Canada",if any product in Candada country has no sales and other country has sales for that product , My requirement is I don't want to show that product in my visualization).
I'm attaching a snap for reference

In the above snap I don't want to show sales for the product "Road-250 Red, 58" and this scenario applies only w.r.to Canada(If any product in Canada has NO Sales).

I tried the below but it's not giving the expected result

Sales Canada =

IF(SELECTEDVALUE('🌎DimSalesTerritory'[SalesTerritoryCountry])="Canada"&&[TotalSales]=BLANK(),1,0)

How to achieve this?
TIA

[likhitha2705]

Hi @gmsamborn  , @DataNinja777 

Your solution helped me but I have extended requirement.
Assume I have a column with status "Competitive(if product contains Yellow name in it) and Non-Competitive(Except Yellow name in it)".I want to show sales for products for Competitive at least level granularity(Which is Product),But at Sub category and Category Level I want to show total for both Competitive and Non-Competitive.
I used this DAX to achieve the result which is 

DOLLAR SALES =

IF(

ISINSCOPE('Products'[EnglishProductName]),

CALCULATE([TotalSales],Products[Category]="Competitive"),

CALCULATE([TotalSales],ALL(Products[Category])))
And I got the correct solution with this.
But now after applying the above DAX which you shared with me gives only Competitive totals at all levels.

Remove Filters =

SUMX('🌎DimSalesTerritory',

IF(

    CALCULATE(

        [TotalSales],

        KEEPFILTERS('🌎DimSalesTerritory'[SalesTerritoryCountry]="Canada"))<>BLANK(),

  1))
This is the output I'm getting.

But I want to see other Sub category and category sales.
Help me out on this.
TIA

[gmsamborn]

Hi @likhitha2705 

I'm a little unclear regarding your requirements.

Using AdventureWorksDW, can you create a pbix that reflects your requirements?

[likhitha2705]

Hi @gmsamborn 
I'm attaching the reference pic and pbix file.
Let's assume I did grouping if Product Name contains "Yellow" in it I'm grouping it as "Competitive" or else "Non-Competitive"(created a column in pbix file as Grouping)
At product level my sales should be only for the Product which the name has Yellow in it or else don't show sales at product Level but show the Sales at Sub category and Category Level(for both Competitive and Non - Competitive). Attaching snap for Reference.

I'm able to achieve the above requirment with the following DAX

DOLLAR SALES =

IF(

ISINSCOPE('Product'[ProductName]),

CALCULATE([TotalSales],'Product'[Grouping]="Competitive"),

CALCULATE([TotalSales],ALL('Product'[Grouping])))

But I don't want to show sales for the products which don't have sales in canada(My Ref Country)
For that I applied the logic which you provided

Sales Canada =

    SUMX(

        'Geography',

        IF(

            CALCULATE(

                [TotalSales],

                KEEPFILTERS( 'Geography'[CountryRegionName] = "Canada" )

            ) <> BLANK(),

            1

        )

    )
This eliminates the products which don't have sales in Canada but at the same time it shows total at sub category and category for "Competitive" only(attached in result sheet in pbix file) but I want to see totals for both Competitive and Non Competitive at Sub Category and Category Level. have to eliminate yellow highlighted products but rest of the totals at sub category and Category should remain the same.

Like-For-Like Canadian Sales v1.pbix 

[gmsamborn]

Hi @likhitha2705 

I see your problem.  The 'Like-For-Like' calculation overrides the 'Competitive/Non-competitive' calculation.  When I wrote the 'Like-For-Like' calculation, there was no other requirements.

I have a page called 'Compare' which shows the effect of the 'Like-For-Like' calculation.

I'm not sure what else I can do at this point.  (I'll take another look.)

Like-For-Like Canadian Sales v1 - mine.pbix

[likhitha2705]

Hi @gmsamborn ..Thanks for the reply.Is there any other way which won't override 'Competitive/Non-competitive' calculation when we apply Sales canada Visual Level Filter

[DataNinja777]

Hi @likhitha2705 

One question..., I am curious why thousand comma separators are in such unusual location. I tried to convert the picture to a table using Power Query, but some of the numbres weren't getting recognized as numbers due to strange location of thousand comma separator.  

Regarding your required output, I think you can achieve it by something like below:

Best regards,

[likhitha2705]

Hello @DataNinja777 
Thanks for the reply ,but I want other countries also in my table.
My requirement is If there's no sales for a Product in CANADA means remove that product sales from other countries also.

[gmsamborn · Accepted solution]

Hi @likhitha2705 

I made a change to the measure supplied by @DataNinja777  .  

Sales Canada = 
    SUMX(
        'Geography',
        IF(
            CALCULATE(
                [Sales],
                KEEPFILTERS( 'Geography'[CountryRegionName] = "Canada" )
            ) <> BLANK(),
            1
        )
    )

I used this to filter the visual.

Like-For-Like Canadian Sales.pbix

Let me know if you have any questions.

(Sorry.  I used DimGeography instead of DimSalesTerritory.)