Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Show target reduction

Hey BI Community,

 

Not sure what term(s) to search for to see if someone has already accomplished what I'm looking to do so if there is a potential solution that someone already knows of and can point me in that direction so I can see if I can reconstruct for my purposes, I would be extremely grateful.  Thanks in advance for any pointers/guidance provided.

 

I have a graph that shows a cumulative expired total by week ending:


What I'd like to accomplish is to create a measure that I can graph right next to each bar to show if we had a reduction of 10 (for easy math) expirations a week, what it'd look like by year end.

 

Essentially the bars I want to graph would follow the logic in this table:

DateIncoming ExpirationsTotalReductionNew Projected Total
9/1141641610406
9/184 (420-416)410 (406+4)10400
9/256 (426-420)406 (400+6)10396
10/24 (430-426)400 (396+4)10390
10/93 (433-430)393 (390+3)10383
10/1615 (448-433)398 (383+15)10388

 

I apologize if this has been resolved so if anyone knows of an example I could look at or could provide me with some pointers on how to create a calculation to perform what I'm trying to accomplish I would greatly appreciate it.  Thanks again in advance for your time, I appreciate it.

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    rsbin ,

    Thank you so much for getting me pointed in the right direction, I wouldn't have been able to accomplish this task without your support.

    I apologize it took me a little longer than anticipated but I have finally been able to put together something that accomplishes what I was trying to do:


    To get this to calculate the reduction properly I had to use the following:

    zTestProjectedReduction = 
VAR minDate = CALCULATE(MIN('table'[weekEndingExpire]),ALLSELECTED('table'[weekEndingExpire]))
VAR maxDate = MAX('table'[weekEndingExpire])
VAR noWeeks = DATEDIFF(minDate, maxDate, WEEK) + 1
VAR noEmps = SELECTEDVALUE('table2'[Value])
VAR compTar = SELECTEDVALUE('table3'[Value])

Return
CALCULATE(
    [zProjectedExpTotSum] - ((noEmps * compTar) * noWeeks),
    'table'[weekEndingExpire] <=maxDate
)

    The difference between my first attempt and the second is I had to "calculate" the minDate because the previous declaration for that variable:

    VAR minDate = MIN('table'[weekEndingExpire])

    would basically default the value to 1 for every week and not take into consideration previous weeks in the date range.

     

    I also ended up created two new tables ("table2" & "table3") that essentially allow the user of the report to select the number of employees and a completion target.  This in turn allows the projection the the bar graph to update dynamically based on the user selection.

     

11 Replies

  • rsbin's avatar
    rsbin
    Community Champion
    3 years ago

    Anonymous ,

    Based on your explanation, this is what I come up with.  Simply create a new Measure:

    NewProjectedTotal = [Total] - [Reduction]

    Then using a Clustered Column Chart and dragging this new Measure, I get this:

    Is this what you are looking for?

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      rsbin ,

      Thanks for the quick response, when I created the measure and dropped it into the visual as you suggested I did not get the same result you did.  I'm not sure if this is because the measure I have calculating the cumulative total messes it up or if perhaps I didn't implement the measure correctly?  Thoughts?

       

      My cumulative total measure is:

       

      My cumulative total measure is:

      zProjectedExpTotCum = 
VAR minDate = MIN('table'[weekEndingExpire])
VAR maxDate = MAX('table'[weekEndingExpire])
VAR tDate = TODAY()

Return

CALCULATE(
    [zProjectedExpTotSum],
    'table'[weekEndingExpire] <= maxDate
)

       

      and the measure I created to calculate the reduction is:

      zTestProjectedReduction = 
[zProjectedExpTotCum] - 10

       

      I'm sure I missed something simple but I'm not sure exactly what...  Thanks again for your response, I appreciate any help / guidance you can provide.

      • rsbin's avatar
        rsbin
        Community Champion
        3 years ago

        Anonymous ,

        You are only subtracting 10 from your Total.

        You need to subtract 10 * numberofweeks.

        Create a [Reduction] measure measure to your [zProjectedExpTotCum]

        to obtain a SUM[Reduction].

        So what you need is the first week reduction is 10, second week reduction = 20, etc...

        This is the table I constructed to get my chart above

        DateIncoming                    Expirations   TotalReduction     NewProjectedTotal

        Sunday, September 11, 2022 416 416 10 406
        Sunday, September 18, 2022 420 410 (406+4) 10 410
        Sunday, September 25, 2022 426 406 (400+6) 10 416
        Sunday, October 2, 2022 430 400 (396+4) 10 420
        Sunday, October 9, 2022 433 393 (390+3) 10 423
        Sunday, October 16, 2022 448 398 (383+15) 10 438

         

        Hope this provides the additional guidance you require.

  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    3 years ago

    Hi Anonymous 

    Thanks for reaching out to us.

    I just want to confirm if you resolved this issue? If yes, you can accept the answer helpful as the solution or share you method and accept it as solution, thanks for your contribution to improve Power BI.

    If you need more help, please let me know.

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      v-xiaotang ,

      My aplogies on the delayed response, I was working through some network issues and requested further assistance.