Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I encountered a problem while applying a filtered measure to the chart. I managed to filter the sum of distinct [Amount] by their respective Flag name, latest year and latest quarter. I have created separated measures for year and quarter and I'm sure they are correct.
Solved! Go to Solution.
Hi @DearestYoki
Due to I don't know your data model, I have to build a sample table to have a test.
Add an Index column in Power Query Editor.
If your table has Date column, you can build a Rank column filtered by Outlet Name, Statement, Year and Qtr.
Rank =
RANKX (
FILTER (
'Table',
'Table'[Outlet Name] = EARLIER ( 'Table'[Outlet Name] )
&& 'Table'[Statement] = EARLIER ( 'Table'[Statement] )
&& 'Table'[Date].[Year] = EARLIER ( 'Table'[Date].[Year] )
&& 'Table'[Date].[QuarterNo] = EARLIER ( 'Table'[Date].[QuarterNo] )
),
'Table'[Index],
,
ASC
)
Result:
You can build a measure to get the 'Distinct' value by get the Min Rank in the values which has the same OuterName, Statement ,Year and Qtr.
Measure = CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Rank]=MIN('Table'[Rank])))
Result:
The result in Red box is right.
If this reply still couldn't help you to solve your problem, please give me a sample table and show me the result you want.
If it is possible, you may tell me your calculated logic and what table will be used to build the measure.
Or you can provide me with your pbix file by your Onedrive for Business.
You can download the pbix file from this link: Filter in measures doesn't work while applying the measure to charts
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@DearestYoki , do you have date. If so use time intelligence with date table
example
QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))
Last complete QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD( ENDOFQUARTER(dateadd('Date'[Date],-1,QUARTER))))
Last to last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-2,QUARTER)))
If not move qtr-year to a different table and create a rank and use it
Qtr Rank = RANKX(ALL(‘Date’),’Date’[Start Of Qtr],,ASC,Dense)
This Quarter = CALCULATE([Sales], FILTER(ALLSELECTED(‘Date’),’Date’[Qtr Rank]=max(‘Date’[Qtr Rank])))
Last Quarter = CALCULATE([Sales], FILTER(ALL(‘Date’),’Date’[Qtr Rank]=max(‘Date’[Qtr Rank])-1))
refer: https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...
Appreciate your Kudos.
Thanks for your reply. How could I add "DISTINCT" into a CALCULATE function since I only want sum of distinict amounts?
Yoki
Hi @DearestYoki
Due to I don't know your data model, I have to build a sample table to have a test.
Add an Index column in Power Query Editor.
If your table has Date column, you can build a Rank column filtered by Outlet Name, Statement, Year and Qtr.
Rank =
RANKX (
FILTER (
'Table',
'Table'[Outlet Name] = EARLIER ( 'Table'[Outlet Name] )
&& 'Table'[Statement] = EARLIER ( 'Table'[Statement] )
&& 'Table'[Date].[Year] = EARLIER ( 'Table'[Date].[Year] )
&& 'Table'[Date].[QuarterNo] = EARLIER ( 'Table'[Date].[QuarterNo] )
),
'Table'[Index],
,
ASC
)
Result:
You can build a measure to get the 'Distinct' value by get the Min Rank in the values which has the same OuterName, Statement ,Year and Qtr.
Measure = CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Rank]=MIN('Table'[Rank])))
Result:
The result in Red box is right.
If this reply still couldn't help you to solve your problem, please give me a sample table and show me the result you want.
If it is possible, you may tell me your calculated logic and what table will be used to build the measure.
Or you can provide me with your pbix file by your Onedrive for Business.
You can download the pbix file from this link: Filter in measures doesn't work while applying the measure to charts
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |