Forum Discussion
Filter with sum in a formula
I'm trying to create a dynamic chart title that will change when filters are applied. The below works except when you apply a filter to the chart.
Chart Ttile1 = "Total transactions "&format(sum(DEET_DATA[Transactions]),"#,##,,.0M")&COMBINEVALUES(" to ",format([DemogsDate].[Date]," Mmmm YYYY"),format([Report_Date].[Date],"Mmmm YYYY"))
Hi Anonymous ,
For a chart title to be dynamic, you need to use a measure. The value will remain static if you use a column. The below need to be enclosed in a function that will return a scalar value to to be used in a measure -MIN, MAX, SELECTEDVALUE, etc.
- [DemogsDate].[Date]
- [Report_Date].[Date]
As a measure, you can rewrite the formula to:
Chart Ttile1 = "Total transactions " & FORMAT ( SUM ( DEET_DATA[Transactions] ), "#,##,,.0M" ) & COMBINEVALUES ( " to ", FORMAT ( MAX ( [DemogsDate].[Date] ), " Mmmm YYYY" ), FORMAT ( MAX ( [Report_Date].[Date] ), "Mmmm YYYY" ) )Of course, you need to replace MAX with the appropriate function if it doesn't return the desired result. The suggestion is solely based on the information provided. If this doesn't resolve your issue, please provide sample data and expected result. Please take time to read this post which is pinned on the Desktop forum page - https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/td-p/1447523/jump-to/first-unread-message
3 Replies
- VijayP
Community Champion
Anonymous
You need to use Selectedvalue function for which you are filtering
miTutorialsSuper User
Please see the below video to create Dynamic Title.
Learn how to create Dynamic Titles in PowerBI | MITutorials - YouTube
- danextian
Hi Anonymous ,
For a chart title to be dynamic, you need to use a measure. The value will remain static if you use a column. The below need to be enclosed in a function that will return a scalar value to to be used in a measure -MIN, MAX, SELECTEDVALUE, etc.
- [DemogsDate].[Date]
- [Report_Date].[Date]
As a measure, you can rewrite the formula to:
Chart Ttile1 = "Total transactions " & FORMAT ( SUM ( DEET_DATA[Transactions] ), "#,##,,.0M" ) & COMBINEVALUES ( " to ", FORMAT ( MAX ( [DemogsDate].[Date] ), " Mmmm YYYY" ), FORMAT ( MAX ( [Report_Date].[Date] ), "Mmmm YYYY" ) )Of course, you need to replace MAX with the appropriate function if it doesn't return the desired result. The suggestion is solely based on the information provided. If this doesn't resolve your issue, please provide sample data and expected result. Please take time to read this post which is pinned on the Desktop forum page - https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/td-p/1447523/jump-to/first-unread-message
