add a category that is not in the table

[minseon]

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 🙂

[talespin · Accepted solution]

hi @minseon ,

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)

            )

        )

)

)