Area Chart Interactions

[kmcd]

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?

[ankitpatira]

@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]

@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]

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

[kmcd]

Hi @Anonymous, I cannot share the original file as the data is commercially sensitive. However I have created a sample workbook with random data although it is behaving slightly differently. How/where do I upload the PBIX file?

[Anonymous]

Hi @kmcd,

You can upload PBIX file to Onedrive and post shared link of the file in this forum.

Thanks,
Lydia Zhang