Categorise Plus or Minus %

[Batman_powerbi]

Hi Team

below is sample data of what i am calculating against

So far i am able to roll up the calculations whether by function or project and obtain a % difference between estimates/Actuals in $ and same for estimate/actuals in days. i do this with measures 

Difference $ =

VAR __BASELINE_VALUE = SUM('Closed (TABLE)'[Estimate $])

VAR __VALUE_TO_COMPARE = SUM('Closed (TABLE)'[Actual $])

RETURN

    IF(

        NOT ISBLANK(__VALUE_TO_COMPARE),

        DIVIDE(__VALUE_TO_COMPARE - __BASELINE_VALUE, __BASELINE_VALUE)

    )

what i am getting stuck with is categorising the differences across either function or project with a "Plus or minus %" category.
Am trying to mark rows that fall within the plus or minus groups of 10%, 20%, 30% etc 

and then be able to count the times each row is counted plus the overall %, similar to output below

what i want to achieve can be done by summarizing the project and function columns into separate related tables and then create columns from calculations. in essence duplicating the same calculations within the new project or function tables.

am finding this a bit too long winded and hopefully learn how to do this simply in the original table through measures and count rows rather than through multiple tables and columns.

hopefully ive been able to explain this

[Ashish_Mathur · Accepted solution]

Hi,

You can solve this with segmentation.  I have solved a similar problem in the attached file.

Hope this helps.

[Batman_powerbi]

@Ashish_Mathur thankyou
i was hoping not to go along the lines of creating a custom table which was a path i was heading towards before i started this thread, but if its the only way around it, then happy to accept this as the solution

thankyou

[Ashish_Mathur]

You are welcome.

[Ashish_Mathur]

You are welcome.

[v-yaningy-msft]

Hi, @Batman_powerbi 

Thanks for @bhanu_gautam positive reply. You can try following dax to achieve your need.

DAX:

Plus/Minus $ Range by Project Column =
VAR __BASELINE_VALUE =
    CALCULATE (
        SUM ( 'Table'[Estimate $] ),
        FILTER ( 'Table', 'Table'[Project] = EARLIER ( 'Table'[Project] ) )
    )
VAR __VALUE_TO_COMPARE =
    CALCULATE (
        SUM ( 'Table'[Actual $] ),
        FILTER ( 'Table', 'Table'[Project] = EARLIER ( 'Table'[Project] ) )
    )
VAR _difference =
    IF (
        NOT ISBLANK ( __VALUE_TO_COMPARE ),
        DIVIDE ( __VALUE_TO_COMPARE - __BASELINE_VALUE, __BASELINE_VALUE )
    )
VAR _plusMinus =
    SWITCH (
        HASONEVALUE ( 'Table'[Estimate $] ),
        _difference <= -0.3
            && _difference > -0.4, "+/- 30%",
        _difference <= -0.2
            && _difference > -0.3, "+/- 20%",
        _difference <= -0.1
            && _difference > -0.2, "+/- 10%",
        _difference <= 0.05
            && _difference > -0.1, "+/- 5%",
        _difference >= 0.05
            && _difference < 0.1, "+/- 5%",
        _difference >= 0.1
            && _difference < 0.2, "+/- 10%",
        _difference >= 0.2
            && _difference < 0.3, "+/- 20%",
        _difference >= 0.3
            && _difference < 0.4, "+/- 30%",
        "+/- 40% or more"
    )
RETURN
    _plusMinus

Count = 
COUNTROWS('Table')

% Overall = 
VAR _allCounts = CALCULATE(COUNTROWS('Table'),ALL('Table'))
VAR _result = DIVIDE([Count],_allCounts)
RETURN
_result

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Batman_powerbi]

@v-yaningy-msft 

unfortunately the sample table and output is incorrect as it is evaluating every row in the table

 

possibly i wasnt clear in my inital post
i wish to calculate and evaluate the Plus/Minus category against Project and Function separately

so in your sample, if i were to calculate this, there should be only a count of 3 total allocated to the %
if i were to do it by Role, i would have a count of 4 total due to the 4 separate roles.

[Batman_powerbi]

@bhanu_gautam 

Any further thoughts were i might be going wrong?

[bhanu_gautam]

@Batman_powerbi , Create a measure for categorizing the difference on the basis of your difference measure

Difference Category =
SWITCH(
TRUE(),
[Difference $] <= -0.3, "-30% or less",
[Difference $] <= -0.2, "-20% to -30%",
[Difference $] <= -0.1, "-10% to -20%",
[Difference $] <= 0.1, "-10% to 10%",
[Difference $] <= 0.2, "10% to 20%",
[Difference $] <= 0.3, "20% to 30%",
"30% or more"
)

Then one more measure for count of category

Count of Categories =

COUNTROWS(

    FILTER(

        'Closed (TABLE)',

        [Difference Category] = EARLIER([Difference Category])

    )

)

And then one for percentage

Percentage of Categories =

DIVIDE(

    [Count of Categories],

    COUNTROWS('Closed (TABLE)')

)

[Batman_powerbi]

@bhanu_gautam 

thankyou for the help

when i create the 2nd measure i obtain an error

"EARLIER/EARLIEST refers to an earlier row context which doesn't exist."

which i am not 100% what that means
any thoughts?

[bhanu_gautam]

@Batman_powerbi , The issue is we have used measure instead of column

Create a caluclated column by going to modelling tab

Difference Category =
VAR diff = 'Closed (TABLE)'[Difference %]
RETURN
SWITCH(
TRUE(),
diff <= -0.3, "-30% or less",
diff <= -0.2, "-20% to -30%",
diff <= -0.1, "-10% to -20%",
diff <= 0.1, "-10% to 10%",
diff <= 0.2, "10% to 20%",
diff <= 0.3, "20% to 30%",
"30% or more"
)

Then create a measure 

Count of Categories =
COUNTROWS(
FILTER(
'Closed (TABLE)',
'Closed (TABLE)'[Difference Category] = MAX('Closed (TABLE)'[Difference Category])
)
)

and last for percentage

DAX
Overall % =
VAR totalRows = COUNTROWS('Closed (TABLE)')
VAR categoryCount = [Count of Categories]
RETURN
DIVIDE(categoryCount, totalRows, 0)

[Batman_powerbi]

thanks @bhanu_gautam 

unfortunately i get a duplication of the count of each time the Project is listed in the data table 

the true nature of the output should be like below,

[bhanu_gautam]

Update measure
Count of Categories =
CALCULATE(
COUNTROWS('Closed (TABLE)'),
ALLEXCEPT('Closed (TABLE)', 'Closed (TABLE)'[Difference Category])
)

[Batman_powerbi]

@bhanu_gautam 
sorry again that is still giving me a duplicate of counts 

i believe its due to the initial calculated column instead of being a measure
- as a column the calculation then assigns a calculation to every row in the table, rather than a rolled up level by project/function