Forum Discussion
Dynamic Table in DAX for Bin
- 9 years ago
Not sure I understand all the requirements, but let me give it a try.
1) Create a calculated table (should be a disconnected table) which will have an extra row for Other. This is the column which will be used in the chart.
ProdSubCat_List = UNION(VALUES(ProductSubcategory[Product Subcategory Name]),
ROW("SubCategoryName", "Other"))2) Create a measure
NewSalesMeasure = VAR SelectedSales = CALCULATE ( [Sales Amount], INTERSECT ( VALUES ( ProductSubcategory[Product Subcategory Name] ), VALUES ( ProdSubCat_List[Product Subcategory Name] ) ) ) VAR AllSelectedSales = CALCULATE ( [Sales Amount], INTERSECT ( VALUES ( ProductSubcategory[Product Subcategory Name] ), ALL ( ProdSubCat_List[Product Subcategory Name] ) ) ) VAR AllSales = CALCULATE ( [Sales Amount], ALL ( 'ProductSubcategory'[Product Subcategory Name] ) ) RETURN IF ( HASONEVALUE ( ProdSubCat_List[Product Subcategory Name] ), SWITCH ( VALUES ( ProdSubCat_List[Product Subcategory Name] ), "Other", AllSales - AllSelectedSales, SelectedSales ), AllSales )3) Create a column chart with ProdSubCat_List[Product Subcategory Name] on axis and NewSalesMeasure on values. Put a slicer which has ProductSubcategory[Product Subcategory Name]
This should work
Update - 3/1/2017 I blogged about this here - http://sqljason.com/2017/03/dynamic-grouping-in-power-bi-using-dax.html
I have also optimized the formula in the blog, in case anyone is interested
Not sure I understand all the requirements, but let me give it a try.
1) Create a calculated table (should be a disconnected table) which will have an extra row for Other. This is the column which will be used in the chart.
ProdSubCat_List = UNION(VALUES(ProductSubcategory[Product Subcategory Name]),
ROW("SubCategoryName", "Other"))
2) Create a measure
NewSalesMeasure =
VAR SelectedSales =
CALCULATE (
[Sales Amount],
INTERSECT (
VALUES ( ProductSubcategory[Product Subcategory Name] ),
VALUES ( ProdSubCat_List[Product Subcategory Name] )
)
)
VAR AllSelectedSales =
CALCULATE (
[Sales Amount],
INTERSECT (
VALUES ( ProductSubcategory[Product Subcategory Name] ),
ALL ( ProdSubCat_List[Product Subcategory Name] )
)
)
VAR AllSales = CALCULATE (
[Sales Amount],
ALL ( 'ProductSubcategory'[Product Subcategory Name] )
)
RETURN
IF (
HASONEVALUE ( ProdSubCat_List[Product Subcategory Name] ),
SWITCH (
VALUES ( ProdSubCat_List[Product Subcategory Name] ),
"Other", AllSales - AllSelectedSales,
SelectedSales
),
AllSales
)3) Create a column chart with ProdSubCat_List[Product Subcategory Name] on axis and NewSalesMeasure on values. Put a slicer which has ProductSubcategory[Product Subcategory Name]
This should work
Update - 3/1/2017 I blogged about this here - http://sqljason.com/2017/03/dynamic-grouping-in-power-bi-using-dax.html
I have also optimized the formula in the blog, in case anyone is interested
Hi SqlJason,
thanks for the awesome reply, I can use that right now! I was wondering how to use this, if I need to have a stacked Slicer with Category and Subcategory? Obvioulsy add both of these columns to the calculated table but I struggle to change the measure to work now 😄
Thanks, really appreciate it!