Forum Discussion

tjlundquist1's avatar
tjlundquist1
Icon for Helper I rankHelper I
6 years ago
Solved

Create a % Running Total (0-100% complete) Measure from a Group Field

Hello. 

 

I am trying to create a visual that will show the % of storm claims reported by # of days from loss to reported. So for example from 0-2 days, 41% of all losses have been reported and all the way to 211-240 days would be 96% of all losses have been reported. 

 

I created this in excel by doing a count of my formula to find the difference between date reported and date of loss, and displayed that result as a "% running total In." Now i'm trying to recreate this in Power BI. "

 

 

 

 

 

 

 

 

 

As you can see above, I've grouped the measure into buckets. However, I want this to display as I explained above - being as the buckets get larger, the % gets closer and closer to 100% of the total. It should be 0-2 is 42 ish% and >365 is 100%. 

 

Below is what the graph looks like in excel. 

Looking for some assistance on a measure that will work with my group fields as the replacement for a column and recreating this chart from excel to Power BI. 

Thank you!

 

Trevor

 

Need Help
  • v-eachen-msft's avatar
    v-eachen-msft
    6 years ago

    Hi tjlundquist1 ,

     

    You could try the following measure:

    Measure =
VAR _table =
    SUMMARIZE (
        Sheet1,
        Sheet1[Group Bucket],
        "a", CALCULATE ( MAX ( Sheet1[Index] ), ALLEXCEPT ( Sheet1, Sheet1[Group Bucket] ) )
    )
VAR _cumulative =
    CALCULATE ( MAX ( Sheet1[Index] ), _table )
VAR _total =
    CALCULATE ( MAX ( Sheet1[Index] ), ALL ( Sheet1 ) )
RETURN
    _cumulative / _total

    Here is my test result.

    Here is the sample data. You need to replace data source with yours.

     

6 Replies

  • v-eachen-msft's avatar
    v-eachen-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi tjlundquist1 ,

     

    I think you need to accumulate your measure.

    At first, you need to add an index column in the query editor. Then refer to the following measure:

    Measure 2 =
SUMX (
    FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Index] <= MAX ( 'Table'[Index] ) ),
    [Measure]
)

    Here is my test result:

     

    • tjlundquist1's avatar
      tjlundquist1
      Icon for Helper I rankHelper I
      6 years ago
       

      That's a great measure. I'm just not getting it to work for me - what did you use for "measure?" Is that a % to grand total measure? I think that's where my problem is in the sumx measure is the expression for that measure.

      For [# of lines] - it's just a dinstinct count of line #'s, which lay out like this format "A######P##.

       

       

      Thanks for your help v-eachen-msft 

       

      • v-eachen-msft's avatar
        v-eachen-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi tjlundquist1 ,

         

        Could you please share your sample data and excepted result to me if you don't have any confidential information. Please upload your files to OneDrive and share the link here.