Show total sum for the year for every bar in the chart in cluster column chart

[Anonymous]

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.

total_sum = CALCULATE(SUM(Sheet1[sales]),

ALLEXCEPT(Sheet1,

Sheet1[year],

Sheet1[bg]),

Sheet1[status] = "Settled")

[v-kkf-msft]

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.

[SolomonovAnton · Accepted solution]

i think it will help you 

measure = CALCULATE(SUM(Sheet1[sales]),ALLSELECTED(Sheet1[month]))

[Anonymous]

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. 

count = CALCULATE(

COUNT('Data'[Claim No]),

ALLSELECTED('Data'[month],'Data'[month_number]),

'Data'[Final Claim Status] in {"Settled", "Outstanding"})

Here I had sorted month by another column month_number and hence included that in Allselected statement and it worked.

Regards,

Amit Darak

[Anonymous]

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

[amitchandak]

@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")

[Anonymous]

Hi,

Unfortunately this does not give the expected result.

Regards,

Amit