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

 

 

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

10 Replies

  • 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

         

         

  • Bokchoy's avatar
    Bokchoy
    Helper II
    5 years ago

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

      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,