Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Slice by Year in Table Visualization

Hi, 

To begin with, I have created 3 measures to calculate Total Fees and do a month-on-month comparison as follows:

1.  TTL Fees = Calculate(sum('Table1'[Amount]), 'Table1'[AccountLevel3]="Fees - Income")

 

2. TTL Fees LM = CALCULATE([TTL Fees],DATEADD('Table1'[Month-Year],-1,MONTH))

 

3. TTL Fees Inc Var = if([TTL Fees LM] = BLANK(), "N/A", [TTL Fees]-[TTL Fees LM])

 

I'm able to generate a table visualization as attached

 

table visualization with the 3 measures

However, I am not able to use a slicer based on my calendar table for this table visualization as it'll result in blanks for the 'TTL Fees LM' measure since it uses 'Table1'[Month-Year]. Kindly advise on how to resolve this.

 

Tks: -Nik

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous !

    You are most likely missing the relation between the Date column of your Table1 and the CalendarTable (in my case DateDIM). And make sure you use the CalendarTable value in your slicer.

     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
    Kind Regards,
    Roland



     

     

     

  • Anonymous's avatar
    Anonymous
    6 years ago

    Thanks for your swift reply, Roland.

     

    I have actually gotten the results as you have gotten too. However, when it comes to slicing to a specific period, I only want to table visual to only display info for the selected period i.e. year(s) in this case.

    In my new visual below, I have selected '2017' but the table visual also/still shows info for '2018' which I don't wish to have:

     

    I need the table visual to only show info for '2017'.

    Thanks: -Nik

  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    Hi Anonymous ,

     

    The issue should be related your measures in the table visual. We can use blank() instead of "N/A". If it doesn't meet your requirement, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

     

  • Anonymous's avatar
    Anonymous
    6 years ago

    Thanks for your message too, Frank.

     

    Yes, after opting to use blank() instead of "N/A", I have managed to solve the problem & now able to do use a year-to-select slicer too.

    Kind regards, -Nik  

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous !

    You are most likely missing the relation between the Date column of your Table1 and the CalendarTable (in my case DateDIM). And make sure you use the CalendarTable value in your slicer.

     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
    Kind Regards,
    Roland



     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks for your swift reply, Roland.

       

      I have actually gotten the results as you have gotten too. However, when it comes to slicing to a specific period, I only want to table visual to only display info for the selected period i.e. year(s) in this case.

      In my new visual below, I have selected '2017' but the table visual also/still shows info for '2018' which I don't wish to have:

       

      I need the table visual to only show info for '2017'.

      Thanks: -Nik

      • v-frfei-msft's avatar
        v-frfei-msft
        Community Support
        6 years ago

        Hi Anonymous ,

         

        The issue should be related your measures in the table visual. We can use blank() instead of "N/A". If it doesn't meet your requirement, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.