Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
likhitha2705
Frequent Visitor

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

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

likhitha2705_0-1712928581901.png

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

1 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.)

View solution in original post

8 REPLIES 8
likhitha2705
Frequent Visitor

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.
likhitha2705_0-1713175115829.png

 

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

Hi @likhitha2705 

 

I'm a little unclear regarding your requirements.

 

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

 

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.

likhitha2705_0-1713204945893.png

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.
likhitha2705_1-1713205795398.png

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

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
Super User
Super User

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.  

DataNinja777_0-1712930162443.png

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

DataNinja777_3-1712931041057.png

Best regards,

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.

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.)

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.