Dynamic column value when drilling up/down

[Bokchoy]

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 🙂

[richbenmintz · Accepted 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

[Bokchoy]

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 

var _val = DIVIDE(CALCULATE(SUM('id ClientBenchmarks'[BenchMark Rev]),(vw_OpsJobs_BI)), [Dynamic color Rev])

[richbenmintz]

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,

[Bokchoy]

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

[richbenmintz · Accepted solution]

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

[richbenmintz]

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

[Bokchoy]

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 🙂

[richbenmintz · Accepted 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

[Bokchoy]

@richbenmintz 
Legend!

[richbenmintz]

Hi @Bokchoy ,

What grain is benchmark, is it by day?

[Bokchoy]

yep currently only have benchmark value by day. Same thing in my actual database also