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

[tjlundquist1]

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

[v-eachen-msft]

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]

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]

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.

[tjlundquist1]

Here is a link to the sample data/ pbix. Expected results in the PBIX.

https://1drv.ms/u/s!Am_I6JawN6C-azxr1Y2C_BhJ-UM?e=C1hx3D 

Thanks for your help @v-eachen-msft 

[v-eachen-msft · Accepted solution]

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.

[tjlundquist1]

@v-eachen-msft - I really appreciate your help with this.

At first, it didn't work - and I didn't understand. I got the same result as I got when I ran your previous SUMX measure, and the only thing that was the same was the INDEX column.

I deleted the INDEX colum and went to the query editor, highlighted the # of days column, created a new INDEX - and I'll be darned it worked!

You're awesome, thanks for working with me!

Trevor