Running total of an assigned value of <X> or less

[mmace1]

There's the graph,  it's made via this one column I made that aggregates everything from an businses-day age calculation in yet another column (and calculating that sucked, btw).

All Age Complete Info = if(CompleteInfo[Business_Days_Age]<1,"< 1 Day",IF(AND(CompleteInfo[Business_Days_Age]>=1,CompleteInfo[Business_Days_Age]<2),"1 to 2 days",IF(AND(CompleteInfo[Business_Days_Age]>=2,CompleteInfo[Business_Days_Age]<3),"2 to 3 days",if(and(CompleteInfo[Business_Days_Age]>=3,CompleteInfo[Business_Days_Age]<4),"3 to 4 days",if(and(CompleteInfo[Business_Days_Age]>=4,CompleteInfo[Business_Days_Age]<5),"4 to 5 days",if(and(CompleteInfo[Business_Days_Age]>=5,CompleteInfo[Business_Days_Age]<6),"5 to 6 days",if(and(CompleteInfo[Business_Days_Age]>=6,CompleteInfo[Business_Days_Age]<7),"6 to 7 days",if(and(CompleteInfo[Business_Days_Age]>=7,CompleteInfo[Business_Days_Age]<8),"7 to 8 days",if(and(CompleteInfo[Business_Days_Age]>=8,CompleteInfo[Business_Days_Age]<9),"8 to 9 days",if(and(CompleteInfo[Business_Days_Age]>=9,CompleteInfo[Business_Days_Age]<10),"9 to 10 days","Over 10 Days"))))))))))

How would I go about showing on the graph say - this is the % that are <1 day, this is the percentage that are (<1 day + 1 to 2 days).   So - this gradually growing line, that would hit 100% at the last category, if I make any sense.

Thanks!

[Eric_Zhang]

@mmace1

You can create a measure as below, replace the CompleteInfo[value] with the actual column/measure in your case. See more details in the attached pbix file.

PERC =
IF (
    MAX ( CompleteInfo[Business_Days_Age] ) >= 10,
    1,
    DIVIDE (
        SUMX (
            FILTER (
                ALLSELECTED ( CompleteInfo ),
                CompleteInfo[Business_Days_Age] <= MAX ( CompleteInfo[Business_Days_Age] )
            ),
            CompleteInfo[value]
        ),
        SUMX ( ALLSELECTED ( CompleteInfo ), CompleteInfo[value] )
    )
)

B

By the way, the calculated column DAX formula can also be

All Age Complete Info = 
SWITCH (
    TRUE (),
    CompleteInfo[Business_Days_Age] < 1, "< 1 Day",
    CompleteInfo[Business_Days_Age] < 2, "1 to 2 days",
    CompleteInfo[Business_Days_Age] < 3, "2 to 3 days",
    CompleteInfo[Business_Days_Age] < 4, "3 to 4 days",
    CompleteInfo[Business_Days_Age] < 5, "4 to 5 days",
    CompleteInfo[Business_Days_Age] < 6, "5 to 6 days",
    CompleteInfo[Business_Days_Age] < 7, "6 to 7 days",
    CompleteInfo[Business_Days_Age] < 8, "7 to 8 days",
    CompleteInfo[Business_Days_Age] < 9, "8 to 9 days",
    CompleteInfo[Business_Days_Age] < 10, "9 to 10 days",
    "Over 10 Days"
)

[mmace1]

SWITCH - that's much more efficient, thanks!

For the PERC calculation - yours works off of a summarized version of the table.  But, my table data is every individual value, as I'm calculating the business-day age for each one.  See screenshot:

 

Would there be a way to modify things to work off of this?   Thanks,

[Eric_Zhang]

---

@mmace1 wrote:

SWITCH - that's much more efficient, thanks!

For the PERC calculation - yours works off of a summarized version of the table.  But, my table data is every individual value, as I'm calculating the business-day age for each one.  See screenshot:

 

Would there be a way to modify things to work off of this?   Thanks,

---

@mmace1

Have you tried my approach? I think it also works for very indivisual values.

[mmace1]

I think it doesn't, because [value] in the equation, refers to the total number of hits in each category.  

But, my data isn't summarized like that, so there's no such [value] column to refer to?