Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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
@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])))
User | Count |
---|---|
104 | |
92 | |
88 | |
78 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |