Forum Discussion

Bokchoy's avatar
Bokchoy
Helper II
5 years ago
Solved

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 condit...
Need Help
Show and Tell
  • richbenmintz's avatar
    richbenmintz
    5 years ago

    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

     

     

  • richbenmintz's avatar
    richbenmintz
    5 years ago

    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's avatar
richbenmintz
Resident Rockstar
5 years ago

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's avatar
    Bokchoy
    Helper II
    5 years ago

    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's avatar
      richbenmintz
      Resident Rockstar
      5 years ago

      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

       

       

    • richbenmintz's avatar
      richbenmintz
      Resident Rockstar
      5 years ago

      Hi Bokchoy ,

       

      What grain is benchmark, is it by day?

      • Bokchoy's avatar
        Bokchoy
        Helper II
        5 years ago

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