cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Bokchoy
Frequent Visitor

Dynamic column value when drilling up/down

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.

Bokchoy_0-1611787690356.png

 

However when i drill up to per/Period, the benchmark value does not change thus making all results green.

Bokchoy_1-1611787812033.png

 

Basically i need my benchmark to change accordingly to my drill level.

 

Please help,

Thanks guys 🙂

 

 

2 ACCEPTED SOLUTIONS

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 

richbenmintz_0-1611798960136.png

and voila, it works at all levels.

richbenmintz_1-1611798986428.png

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

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

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 

richbenmintz_0-1611873229686.png

at period month level and 

richbenmintz_1-1611873271007.png

at the day level.

Richard





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

10 REPLIES 10
Bokchoy
Frequent Visitor

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




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,





Did I answer your question? Mark my post as a solution!

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 

richbenmintz_0-1611873229686.png

at period month level and 

richbenmintz_1-1611873271007.png

at the day level.

Richard





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

richbenmintz
Super User I
Super User I

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

 





Did I answer your question? Mark my post as a solution!

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 

richbenmintz_0-1611798960136.png

and voila, it works at all levels.

richbenmintz_1-1611798986428.png

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

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

@richbenmintz 
Legend!

 

Hi @Bokchoy ,

 

What grain is benchmark, is it by day?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors