Forum Discussion

kmcd's avatar
kmcd
Frequent Visitor
9 years ago

Area Chart Interactions

I have an area chart which shows target sales and actual sales against month.

 

The targets table has the columns:

  • Team
  • Month
  • Target

Whilst the sales table has the columns:

  • SaleID
  • SaleDate
  • SaleMonth (calculated column)
  • UserID (with a relationship to the users table to find the team)

The target table has data until the end of our business year whilst the sales table has data to date. I can filter the report on team and month but have set the month filter to have no action on the area chart.

 

Before filtering on team the area chart shows target data for the rest of the year but when I filter on team it only shows data for the months that there is sales data.

 

Any ideas how I can maintain all target data when I filter by team?

 

 

Need Help

5 Replies

  • ankitpatira's avatar
    ankitpatira
    Community Champion
    9 years ago

    kmcd You can create dax measure using below and use that for target in visual. So when applying any filter won't have any effect on that.

     

    =CALCULATE( SUM('YOURTABLE'[targetColumn]), ALL('YOURTABLE'))

    • kmcd's avatar
      kmcd
      Frequent Visitor
      9 years ago

      ankitpatira thanks for the reply. I tried your DAX and it resolves the filtering issue but fixes the annual target value as target for each month. Using ALL('YOURTABLE'[targetColumn]) instead of ALL('YOURTABLE') resolved this but the filtering reverts back to my original problem.

       

      Any ideas?

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Hi kmcd,

        Could you please share sample data of your tables and post the expected result here? We will appreciate that if you can share the PBIX file.

        Thanks,
        Lydia Zhang