Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Matrix: Getting rid of blank spaces

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? 

 

 

Need Help
Tips and Tricks

3 Replies

  • Joe_Barry's avatar
    Joe_Barry
    Icon for Solution Sage rankSolution Sage
    2 years ago

    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

  • suparnababu8's avatar
    suparnababu8
    Icon for Super User rankSuper User
    2 years ago

    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!

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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