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

     

v-eachen-msft's avatar
v-eachen-msft
Icon for Community Support rankCommunity Support
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.

 

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

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