Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
I have ORDER table and I am needing to count rows based on month/activity/stage. Below is an example of the data and what I am trying to accomplish.
count of activity stage by month where
activity = ready & stage > 1
activity = shipped & stage > 2
activity = delivered & stage >= 3
Data:
Month Activity Stage
May Ready 1
May Shipped 2
May Shipped 2
May Delivered 3
May Delivered. 3
June Ready 1
Results:
Month Activity Stage Progress Count
May Ready 4
May Shipped 2
May Delivered 2
June Ready 1
I have tried the below but out of ideas.
COUNTROWS( FILTER( ORDER, Order[month] = EARLIER(Order[month]) && Order[stage] > EARLIER(order[stage]) ) ), ALLEXCEPT(order, order[activity]) )
GROUPBY( 'ORDER', 'ORDER'[Month], 'ORDER'[Activity], "Stage Progress Count", CALCULATE( COUNTAX( FILTER( VALUES('ORDER'), 'ORDER'[Month] = EARLIER('ORDER'[Month]) && ( ('ORDER'[Activity] = "Ready" && 'ORDER'[Stage] > 1) || ('ORDER'[Activity] = "Shipped" && 'ORDER'[Stage] > 2) || ('ORDER'[Activity] = "Delivered" && 'ORDER'[Stage] >= 3) ) ), 1 ) ) )
Solved! Go to Solution.
output
NB : base on the condition you wrote, june shouldnt display,
"" activity = ready & stage > 1 "" for june, stage is = 1 , but the condition is taking stage > 1 only .
measure :
Measure 3 =
var ds =
ADDCOLUMNS(
SUMMARIZE(
Table5,
Table5[Month],
Table5[Stage],
Table5[ Activity]
),
"@c" ,
var m = Table5[Month]
var s = Table5[Stage]
var a = Table5[ Activity]
return
SWITCH(
TRUE(),
a = "Ready" ,
CALCULATE(
COUNTROWS(Table5),
ALLEXCEPT(Table5,Table5[Month]),
Table5[Stage]>1
),
a = "Shipped" ,
CALCULATE(
COUNTROWS(Table5),
ALLEXCEPT(Table5,Table5[Month]),
Table5[Stage]>2
),
a = "Delivered" ,
CALCULATE(
COUNTROWS(Table5),
ALLEXCEPT(Table5,Table5[Month]),
Table5[Stage]>=3
)
)
)
RETURN
SUMX(ds,[@c])
let me know if this helps
If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠
output
NB : base on the condition you wrote, june shouldnt display,
"" activity = ready & stage > 1 "" for june, stage is = 1 , but the condition is taking stage > 1 only .
measure :
Measure 3 =
var ds =
ADDCOLUMNS(
SUMMARIZE(
Table5,
Table5[Month],
Table5[Stage],
Table5[ Activity]
),
"@c" ,
var m = Table5[Month]
var s = Table5[Stage]
var a = Table5[ Activity]
return
SWITCH(
TRUE(),
a = "Ready" ,
CALCULATE(
COUNTROWS(Table5),
ALLEXCEPT(Table5,Table5[Month]),
Table5[Stage]>1
),
a = "Shipped" ,
CALCULATE(
COUNTROWS(Table5),
ALLEXCEPT(Table5,Table5[Month]),
Table5[Stage]>2
),
a = "Delivered" ,
CALCULATE(
COUNTROWS(Table5),
ALLEXCEPT(Table5,Table5[Month]),
Table5[Stage]>=3
)
)
)
RETURN
SUMX(ds,[@c])
let me know if this helps
If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠
User | Count |
---|---|
85 | |
81 | |
64 | |
53 | |
46 |
User | Count |
---|---|
101 | |
49 | |
42 | |
39 | |
38 |