Clustered column chart for monthly % share for every year

[Anonymous]

Hi,

I am trying to recreate following excel chart in Power BI.

I have data in raw format. Logic is I want to see every year's monthly trend.
Aug-21 it will be 120/846 ie 14%, Sep-21 it will be 107/846 ie 13%.

Aug-20 it will be 79/1124 ie 7%,  Sep-20 it will be103/1124 ie 9% and so on.

I tried writing below dax but giving incorrect result.

perc_share = DIVIDE(CALCULATE(COUNT(data[claim_no]),
FILTER(data, data[status] in {"Settled", "Outstanding"})),
CALCULATE(COUNT(data[claim_no]),
ALLEXCEPT(data,data[year]),
data[status]in {"Settled", "Outstanding"}))

I also tried using ALL filter. But still unable to get required result.

Kindly help.

Amit Darak

[v-yangliu-msft · Accepted solution]

Hi  @Anonymous ,

I created some data:

Here are the steps you can follow：

1. Create calculated column.

Year = YEAR('data'[claim_no])

Month = FORMAT('data'[claim_no],"mmm")

2. Create measure.

Measure =
var _group1=CALCULATE(COUNT('data'[claim_no]),FILTER(ALL(data),'data'[Year]=MAX('data'[Year])&&'data'[Month]=MAX('data'[Month])&&'data'[status] in {"Settled", "Outstanding"}))
var _group2=CALCULATE(COUNT('data'[claim_no]),FILTER(ALL(data),'data'[Year]=MAX('data'[Year])&&'data'[status]in {"Settled", "Outstanding"}))
return
DIVIDE(
    _group1,_group2)

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[amitchandak]

@Anonymous , if you have date using date table get total that year

example

Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year), "7/31"),"7/31"))

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),previousyear('Date'[Date],1,Year), "7/31")

or

This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))