Matrix: Getting rid of blank spaces

[Anonymous]

Hi everyone, 

I am trying to build a Matrix with two different categories A and B as 'Values' in the  visualization panel and months as columns.  I am doing this, because I want to put a different color in the bars (orange or green). But now I am getting two values for January, Februry and so on. This makes no sense, because there is only one value for Category A and one for Category B for month.

How can I get rid of those blank spaces in between? So I only have on value for month. 

Aditionally I would also like to have in the same matrics two calculated measures: Category A last year and Category B last year 🙂 How can I get it? 

[Anonymous]

Hi @Anonymous 

Did Joe_Barry 's and suparnababu8's methods solve your problem? If yes, could you please mark them as solutions? This will be of great help to other users experiencing similar problems. Thank you!

If you still have questions, please feel free to ask me.

Best Regards,
Yulia Xu

[suparnababu8]

Hi @Anonymous 

By using ISBLANK function you can handle the blanks
First create this measure to handle blanks as zero's. 

A = IF(ISBLANK(SUM(Table[CategoryA])), 0, SUM(Table[CategoryA]))
B = IF(ISBLANK(SUM(Table[CategoryB])), 0, SUM(Table[CategoryB]))

Then you create this easure 

CategoryALastYear = 
CALCULATE(SUM(Table[CategoryA]),
    SAMEPERIODLASTYEAR('Calendar'[Date])
)

category B 

CategoryBLastYear = 
CALCULATE(SUM(Table[CategoryB]),
    SAMEPERIODLASTYEAR('Calendar'[Date])
)

Then, drag the new measures (CategoryALastYear and CategoryBLastYear) into the Values field of your matrix visual.

Hope this helps you!

[Joe_Barry]

Hi @Anonymous 

Do you have the categories in the column field? if yes, then this expected behaviour. It will happen too, if you put it in the rows.

Regarding the calculations:

If your data model is set up in a Star schema and you have a date table, these measures will help you.

TotalYTD = 
TOTALYTD(SUM(Table[Amount]), Date[Date]) 

TotalPY =
CALCULATE(
    [TotalYTD], 
      DATEADD(Date[Date], -1 , YEAR)
)

Just add the Category to a visual and both measures. Use the Columns from the Date table in all visuals and slicers.

Joe