Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Remove filters from text column based on slicer selection

Hi All

Below table shows:

2019 COT - total Volume for 2019 but if 2023 is selected in the filter then YtD 2019
COT (Period Covered) - Sum of Volume for years selected in a filter.

 

I face an issue when 2023 is selected in a filter that sum of 2019 COT is not equal sum of all rows shown in a table. It is because rows which have data for 2019 and don't have for 2023 are not shown in a table but are included in a total.

 

Does anyboady know how to show all rows in a table when 2023 is selected in the filter?

 

Thanks in advance!!

 

 

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    Anonymous , Not very clear, but you can use the option on the right click of group bys of visual - Show Item with no data

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thanks amitchandak  but it didn't help.

       

      2019 COT = CALCULATE([COT (Period Covered)], 'Weekly COT_Database'[Year] = 2019, DATESYTD(Date_Lookup[End of Week Date]))

      COT (Period Covered) = calculate(SUM('Weekly COT_Database'[Volume]), DATESYTD(Date_Lookup[End of Week Date]))
       
      These are the measures I use. First measure should always show Volume for 2019. The second measure should show Volume for a period selected in a slicer. When I select 2023 in a slicer the first measure total is correct but the sum of all rows shown in a table is not matching total. It is becuse some rows have data for 2019 but don't have for 2023.
      Hope now it is more clear.