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 guys,
Ive got a matrix table here for benchmarking results and i want my benchmark value to dynamically change based on my drill level which is year,month,period or day.
i also have conditional format set up: [if result is >85% of benchmark, green; if result is between 75%-84.99% of benchmark yellow; else red]
Please see table below
Within my data, i only have benchmark numbers for Per/day level. When drilled all the way down it works perfectly fine.
However when i drill up to per/Period, the benchmark value does not change thus making all results green.
Basically i need my benchmark to change accordingly to my drill level.
Please help,
Thanks guys 🙂
Solved! Go to Solution.
Hi @Bokchoy ,
This what I am thinking, create measure to use as your benchmark amount
bench measure = SELECTEDVALUE(Bench_mark[Benchmark]) * COUNTROWS('Calendar')
essentially multiplies the benchmark by the number of days in each period, when drill down to the day would be 1, when looking at a month would be the number of days visible in the selected month, etc...
Then create a conditional formatting measure,
indicator value =
var _val = DIVIDE(COUNTROWS('Job_orders'), [bench measure])
return
SWITCH(true(),
_val >= .85, "Green",
_val >= .75, "Yellow",
"Red"
)
you then create a conditional format by field value
and voila, it works at all levels.
Attached please find your pbix with the mods, Although this is not my favourite paractice I had to make the relatioship between bench_mark and job_orders bi-directional to remove the cros join when date is introduced
I hope this helps
Proud to be a Super User!
Hi @Bokchoy ,
you would modify the measures like so based on the sample
rev bench measure = SELECTEDVALUE('Table'[Benchmark_rev]) * COUNTROWS('Calendar')
indicator value Rev=
var _val = DIVIDE(sum(Job_orders[Sales Rev]), [dollar bench measure])
return
SWITCH(true(),
_val >= .85, "Green",
_val >= .75, "Yellow",
"Red"
)
results in
at period month level and
at the day level.
Richard
Proud to be a Super User!
Hi @richbenmintz
Now that we have the count of jobs part done. I also need to do the same for Benchmark Revnue. The logic is the same however the DAX function needs to be changed.
i am having trouble to Sum Revenue based on the selected range of days. refer to below
Hi @Bokchoy ,
Could you put your new measures in the sample pbix and share? Not sure what
[Dynamic Color Rev]
measure does, also not sure what the calculate is for and why you are using the vw_OpsJobs_BI table as a filter condition.
Thanks,
Proud to be a Super User!
Hi sorry for badly explaining it. Basically i would like to do the same for Revenue benchmark. In this case we are no longer counting the rows but need value of sales rev.
Please see link for new sample dataset.
https://drive.google.com/file/d/1Ey0NsOrnBelRX06lqusc5oMs2yr1ZE3J/view?usp=sharing
Hi @Bokchoy ,
you would modify the measures like so based on the sample
rev bench measure = SELECTEDVALUE('Table'[Benchmark_rev]) * COUNTROWS('Calendar')
indicator value Rev=
var _val = DIVIDE(sum(Job_orders[Sales Rev]), [dollar bench measure])
return
SWITCH(true(),
_val >= .85, "Green",
_val >= .75, "Yellow",
"Red"
)
results in
at period month level and
at the day level.
Richard
Proud to be a Super User!
Hi @Bokchoy ,
Are you able to provide a sample pbix file or sample data in a table, I think you will be able to accomplish what you want with scope assignments based on the level of the hierarchy that the intersection of data represents something like
conditional value = switch(true(),
hasonevalue(level3), calculate(something),
hasonevalue(level2), calculate(somethingel2),
hasonevalue(level1), calculate(somethingel3)
)
Proud to be a Super User!
Hi @richbenmintz ,
thanks for looking into this. I made a super simple version of the database. please see below link
Hope this helps
Thank you 🙂
Hi @Bokchoy ,
This what I am thinking, create measure to use as your benchmark amount
bench measure = SELECTEDVALUE(Bench_mark[Benchmark]) * COUNTROWS('Calendar')
essentially multiplies the benchmark by the number of days in each period, when drill down to the day would be 1, when looking at a month would be the number of days visible in the selected month, etc...
Then create a conditional formatting measure,
indicator value =
var _val = DIVIDE(COUNTROWS('Job_orders'), [bench measure])
return
SWITCH(true(),
_val >= .85, "Green",
_val >= .75, "Yellow",
"Red"
)
you then create a conditional format by field value
and voila, it works at all levels.
Attached please find your pbix with the mods, Although this is not my favourite paractice I had to make the relatioship between bench_mark and job_orders bi-directional to remove the cros join when date is introduced
I hope this helps
Proud to be a Super User!
Hi @Bokchoy ,
What grain is benchmark, is it by day?
Proud to be a Super User!
yep currently only have benchmark value by day. Same thing in my actual database also
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 |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |