'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".)
- [Sales Percentage]
Sales Percentage = [Internet Total Sales] / [Visual Total]
- [Visual Total]
Visual Total = CALCULATE([Internet Total Sales], ALLSELECTED(DimProductCategory))
- [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.
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 |
|---|

