cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
stwg Frequent Visitor
Frequent Visitor

histogram or bell curve - negative numbers

Hi All;

 

Newbie here and of course on a tight schedule.  Been working this issue for 30 hours alreday, and my brian is now too twisted to figure this out.  Hope there is help out there.

 

I first tried to work with a bell curve - have the curve, but don't know how to plot my data on it so I switched to working on a Histogarm - But I only have 1/2 my data (all the positive ratios, none of the negative ratios).

 

The histogram should assume the shape of the bell curve.

My ratios range from -2000 to positive 400

Bins should be...open to suggestions, but right now I'm using -2000 to -37, -36.99 to -25, -24.99 to -13, -12.99 to -0.01, 0-12.99, 13 to 24.99, 25 to 36.99, 37 to 2000

 

here's my Catagory table:

CategoryLLUL
Neg 37 -2000-37.00%-2000.00%
Neg 25 -36-25.00%-36.99%
Neg 13 -24-13.00%-24.99%
Neg 0 -120.00%-12.99%
Pos 0-120.00%12.99%
Pos 13-2413.00%24.99%
Pos 25-3625.00%36.99%
Pos 37-200037.00%

2000.00%

 

 

And here's my column:

RankCategory = VAR CatVar=[ROE] RETURN CALCULATE (VALUES('benchmark'[Category]), CatVar>'benchmark'[LL],CatVar<='benchmark'[UL])
 
here is my ratio column and my result column:
ROERankCategory
-41.02% 
-30.87% 
-27.33% 
-26.75% 
-25.87% 
-22.26% 
-9.87% 
-6.89% 
-5.51% 
-2.41% 
-1.65% 
0.11%Pos 0-12
0.32%Pos 0-12
4.82%Pos 0-12
5.47%Pos 0-12
7.00%Pos 0-12
8.37%Pos 0-12
9.52%Pos 0-12
10.56%Pos 0-12
12.34%Pos 0-12
15.40%Pos 13-24
15.90%Pos 13-24
17.09%Pos 13-24
25.98%Pos 25-36
26.38%Pos 25-36
26.41%Pos 25-36
26.59%Pos 25-36
40.81%Pos 37-2000
 ***As I was creating this, I found that this isn't always putting the ROE into the right BIN, which is a problem too
 
Thanks for any help or guidance
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: histogram or bell curve - negative numbers

Hi @stwg ,

 

RankCategory = 
VAR CatVar = [ROE]
RETURN
    IF (
        CatVar > 0,
        CALCULATE (
            VALUES ( 'benchmark'[Category] ),
            CatVar > 'benchmark'[LL],
            CatVar <= 'benchmark'[UL]
        ),
        CALCULATE (
            VALUES ( 'benchmark'[Category] ),
            CatVar <= 'benchmark'[LL],
            CatVar > 'benchmark'[UL]
        )
    )

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
HotChilli Established Member
Established Member

Re: histogram or bell curve - negative numbers

You're nearly there with your classification function.  However, the reason it's only working for half the data is that you switch the way values are categorised in the category table.  +ve number categories are mirrored by -ve number categories when they should be treated just the same. UL (upper Level) of the -ve numbers is actually the lower level.  Example -25% is a LL and -36.99% is an UL (They should be switched. Maybe the best way to think about this is to draw a number line)

 

To summarise, swap your UL and LL values for all negative numbers in the category table,

stwg Frequent Visitor
Frequent Visitor

Re: histogram or bell curve - negative numbers

Thank you so much!!  I had a feeling I was close, but just couldn't figure it out.  Thanks again.

Community Support Team
Community Support Team

Re: histogram or bell curve - negative numbers

Hi @stwg ,

 

RankCategory = 
VAR CatVar = [ROE]
RETURN
    IF (
        CatVar > 0,
        CALCULATE (
            VALUES ( 'benchmark'[Category] ),
            CatVar > 'benchmark'[LL],
            CatVar <= 'benchmark'[UL]
        ),
        CALCULATE (
            VALUES ( 'benchmark'[Category] ),
            CatVar <= 'benchmark'[LL],
            CatVar > 'benchmark'[UL]
        )
    )

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.