'Power BI-DAX: ALLSELECTED Vs ALL - problem while trying to obtain Visual Total and Non-Visual Total

I have a situation similar to the one below. I have used AdventureWorksDW database to demonstrate.

I have a simple measure called [Internet Total Sales] which sums the Sales Amount column in the FactInternetSales table.

I have the dimensions - Product Categories, Geography, Date (please refer to the Power BI .pbix file)

In the first page of the Power BI file, I have a simple table to illustrate the [Internet Total Sales] measure.

In the second page, I am developing a new DAX based Calculated Table, using 3 new measures apart from [Internet Total Sales].

CT = CALCULATETABLE(

                        SUMMARIZE(

                                    FactInternetSales,

                                    ROLLUP(DimProductCategory[EnglishProductCategoryName]),

                                    "Sales", [Internet Total Sales],

                                    "Sales Percentage", [Sales Percentage],

                                    "Visual Total", [Visual Total],

                                    "Non Visual Total", [Non Visual Total]

                                  ),

                         DimDate[CalendarYear] = 2007,

                         DimGeography[EnglishCountryRegionName] = "United States",

                         FILTER(

                                 DimProductCategory,

                                 DimProductCategory[EnglishProductCategoryName] = "Accessories"

                                 ||

                                 DimProductCategory[EnglishProductCategoryName] = "Clothing"

                               )

                      )

(The [Internet Total Sales] is aliased as "Sales".)

  1. [Sales Percentage]

Sales Percentage = [Internet Total Sales] / [Visual Total]

  1. [Visual Total]

Visual Total = CALCULATE([Internet Total Sales], ALLSELECTED(DimProductCategory))

  1. [Non Visual Total]

Non Visual Total = CALCULATE([Internet Total Sales], ALL(DimProduct))

While the [Visual Total] is correct, the [Non Visual Total] isn't, when filtered only for two of the four categories.

enter image description here

The Calculated Table is in Page 2. I am filtering for [UnitedStates]-[2007]-[Accessories,Clothing categories only]. The [Non Visual Total] must read $9.346 Million in all the 3 rows of Page 2. (i.e. United States filter, 2007 filter, all the Product Categories). Insead I am seeing the "Sales" value (i.e. [Internet Total Sales]) itself.

Any idea how to correct this ? Please see the Power BI file. Let me know if you need more information. Can you validate the Calculate Table DAX formula for the table 'CT' , am I doing it right in the way I am filtering the Accessories and Clothing using a FILTER inside CALCULATEBLE function ?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source