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,
I need to create a measure where I am showing total sum for the year for every bar in the chart.
As can be seen from the below image is a dummy data.
This is what my output is when no month filter is selected. Basically 550 is sum for 2021, 309 is sum for 2022 and is showing for every month which is correct.
But when I select month filter, in this case April, May it should not show 550, instead it should show 89 for 2021 and 146 for 2022.
Below is the DAX I have written. Any suggestion how to modify dax.
Solved! Go to Solution.
i think it will help you
measure = CALCULATE(SUM(Sheet1[sales]),ALLSELECTED(Sheet1[month]))
Hi @Anonymous ,
It depends on whether the year and month fields you use in the matrix come from the same table.
For example, if the year in the matrix comes from Table and the month comes from the Dates table, then you need to use that measure.
total_sum =
CALCULATE (
SUM ( 'Table'[Sales] ),
ALLSELECTED ( 'Dates' ),
'Table'[status] = "Settled"
)
If both month and year come from the Dates table, then use the measure.
total_sum 2 =
CALCULATE (
SUM ( 'Table'[Sales] ),
FILTER ( ALLSELECTED ( 'Dates' ), Dates[Year] = MAX ( Dates[Year] ) ),
'Table'[status] = "Settled"
)
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
i think it will help you
measure = CALCULATE(SUM(Sheet1[sales]),ALLSELECTED(Sheet1[month]))
Hi,
Came to know from some other post that if your column is sorted by another column you need to have both of them in the all statement. That worked for me.
Hi,
Strangely the same dax when I executed in my original report, it did not work. Please find below image. It shows monthly value for each month's bar and not sum of total month's selected.
Where as when I use the below formula, it gives me correct total for year, but does not change when I select month filter.
monthly_count = CALCULATE(COUNT(data[claim_no]),
ALLEXCEPT(data,
data[year],
data[claim_type],
'data'[bg]),
data[status] in {"Settled", "Outstanding"})
Any idea what am I missing here?
Thank You.
Regards,
Amit
@Anonymous , try one or the two
total_sum = CALCULATE(SUM(Sheet1[sales]),
filter(Sheet1,
Sheet1[status] = "Settled"))
or
total_sum = CALCULATE(SUM(Sheet1[sales]),
ALLEXCEPT(Sheet1,
Sheet1[year],Sheet1[month],
Sheet1[bg]),
Sheet1[status] = "Settled")
Hi,
Unfortunately this does not give the expected result.
Regards,
Amit
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 |
---|---|
114 | |
101 | |
78 | |
75 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |