add a category that is not in the table

minseon — Thu, 08 Feb 2024 02:36:11 GMT

Hi all

Is it possible to add a category that is not in the table?

i have data like below

and i want to display like below

A and B categories are month-to-date (MTD), and the C category is a fixed value regardless of the date slicer.

and result that i want is this

until now
1. user matrix chart -> row(categort), column(category2)

2. i made dax that display mtd and monthly total

Using DAX, it is possible to implement something like the 3rd picture??

Thanks all 🙂

Re: add a category that is not in the table

talespin — Thu, 08 Feb 2024 11:01:33 GMT

This is a dirty solution, if it works for you. Better doing it in Power Query.

MTD Val =

VAR _Dt = SELECTEDVALUE(TestTbl5[Dt])

VAR _cat = SELECTEDVALUE( TestTbl5[Category])

VAR _cat2 = SELECTEDVALUE(TestTbl5[Category2])

VAR _Return = IF( _cat = "A" && _cat2 = "c", 100,

IF( _cat = "B" && _cat2 = "c", 200,

CALCULATE( [SumVal], TestTbl5[Dt] <= _Dt )

)

RETURN IF( HASONEFILTER(TestTbl5[Category]) && HASONEFILTER(TestTbl5[Category2]), _Return,

VAR _Aa = CALCULATE( [SumVal], TestTbl5[Dt] <= _Dt, TestTbl5[Category] = "A", TestTbl5[Category2] = "a")

VAR _Ab = CALCULATE( [SumVal], TestTbl5[Dt] <= _Dt, TestTbl5[Category] = "A", TestTbl5[Category2] = "b")

VAR _Ac = CALCULATE( [SumVal], TestTbl5[Dt] <= _Dt, TestTbl5[Category] = "A", TestTbl5[Category2] = "c")

VAR _Ba = CALCULATE( [SumVal], TestTbl5[Dt] <= _Dt, TestTbl5[Category] = "B", TestTbl5[Category2] = "a")

VAR _Bb = CALCULATE( [SumVal], TestTbl5[Dt] <= _Dt, TestTbl5[Category] = "B", TestTbl5[Category2] = "b")

VAR _Bc = CALCULATE( [SumVal], TestTbl5[Dt] <= _Dt, TestTbl5[Category] = "B", TestTbl5[Category2] = "c")

RETURN

IF( NOT(HASONEFILTER(TestTbl5[Category])) && HASONEFILTER(TestTbl5[Category2]),

IF( _cat2 = "a", _Aa/_Ba,

IF( _cat2 = "b", _Ab/_Bb,

IF( _cat2 = "c", 100/200)

)