Forum Discussion

mithrandir's avatar
mithrandir
Icon for Helper I rankHelper I
9 years ago

How to make a dynamically calculated column (or equivilant measure) based on visual filter?

So, I'm using Power BI to measure my teams JIRA kanban data; for those who aren't familiar with JIRA, it's an issue tracking tool commonly used for software development. The functionality I'm trying to create is giving every row in an 'Issues' table a rank based on standard deviation based on the time it took to resolve the issue. Then be able to filter my visual by a date value and have the rank for each row dynamically change for a new standard deviation taken from the given time frame.

sdRankTotalDaysSpent = 
VAR mean = AVERAGE(GetIssues[totalDaysSpent])
VAR stdD = STDEV.P(GetIssues[totalDaysSpent])
RETURN
SWITCH(
    TRUE(),
    [totalDaysSpent] > (mean + (3 * stdD)),
    7,
    [totalDaysSpent] <= (mean + (3 * stdD)) &&
        [totalDaysSpent] > (mean + (2 * stdD)),
    6,
    [totalDaysSpent] <= (mean + (2 * stdD)) &&
        [totalDaysSpent] > (mean + stdD),
    5,
    [totalDaysSpent] <= (mean + stdD) &&
        [totalDaysSpent] > mean,
    4,
    [totalDaysSpent] <= mean &&
        [totalDaysSpent] > (mean - stdD),
    3,
    [totalDaysSpent] <= (mean - stdD) &&
        [totalDaysSpent] > (mean - (2 * stdD)),
    2,
    [totalDaysSpent] <= (mean - (2 * stdD)) &&
        [totalDaysSpent] > (mean - (3 * stdD)),
    1,
    [totalDaysSpent] <= (mean - (3 * stdD)),
    0)


The problem with creating the column above is that the values are stored, so even though my count of each rank can be filtered, the ranks themselves do not change based on a new standard deviation. I've created measures for the mean and standard deviation so that they are dynamic, but i'm having trouble translating that to each row.

Any help is much appreciated!

Need Help

8 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    HI mithrandir

     

    IT should be possible to generate a measure to do what you need.  And a measure will react to filters etc.

     

    If you post a small sample of your data, including a mockup of what you expect your results to look like, we can try and help you out.

    • mithrandir's avatar
      mithrandir
      Icon for Helper I rankHelper I
      9 years ago

      For the sake of discussion, I created mock data to illustrate what I care about.

       

       

       

      So, for each row, I currently have a column that simply gives the row a rank from 0-7 based on the number of standard deviations from the mean (left or right respectively). That calculated column formula is the one on the original post. The problem is, on a page in my report, if I add a filter slider by createDate then the counts of each rank (say in a histogram) adjust based on my filter, but the standard deviation and mean are not recalculated. Creating measures for the mean and standard deviation are the obvious fix for that, but I didn't know how to keep the rank column and have the value change dynamically or put it into a measure that evaluates for each column.

      Thanks for the quick response Phil_Seamark!