Return employee number closest to 80%

[Anonymous]

I have a graph that in axis X there is an employee and in axis Y there is the amount of faults he solved.

In addition there is a line that is a cumulative percentage of faults solved.(Measure)
 if the total is 787 and employee number 1 solved 144 faults
So employee number 1 solved 18 percent of the total.
Employee number 2 solved 89 which is 11 percent and another 18 percent that employee number 1 solved but has 29 percent shown.

I need help creating an measure that will return the number of employees closest to 80 percent.
In other words, in this case, I want an measure that will return employee number 12, since his cumulative percentage is 79.22.

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how you wrote cumulative percentage measure, but please check the below picture and the attached pbix file.

result engineer measure: = 
VAR _currentengineer =
    MAX ( Data[FW Engineers] )
VAR _allresolved =
    CALCULATE ( [Resolved measure:], ALL ( Data[FW Engineers] ) )
VAR _cumulatepercenttable =
    ADDCOLUMNS (
        ADDCOLUMNS (
            ALL ( Data[FW Engineers] ),
            "@allresolved", _allresolved,
            "@cumulateresolved",
                CALCULATE (
                    SUMX (
                        FILTER ( ALL ( Data ), Data[FW Engineers] <= MAX ( Data[FW Engineers] ) ),
                        Data[Resolved]
                    )
                )
        ),
        "@cumulatepercent", [@cumulateresolved] / [@allresolved]
    )
VAR _lowerthan80percentmax =
    MAXX (
        FILTER ( _cumulatepercenttable, [@cumulatepercent] <= 0.8 ),
        [@cumulatepercent]
    )
VAR _upperthan80percentmin =
    MINX (
        FILTER ( _cumulatepercenttable, [@cumulatepercent] > 0.8 ),
        [@cumulatepercent]
    )
VAR _closestvalue =
    MIN (
        ABS ( 0.8 - _lowerthan80percentmax ),
        ABS ( 0.8 - _upperthan80percentmin )
    )
VAR _closestpercentage =
    IF (
        _closestvalue
            = ABS ( 0.8 - _lowerthan80percentmax ),
        _lowerthan80percentmax,
        _upperthan80percentmin
    )
VAR _resultengineer =
    SUMMARIZE (
        FILTER ( _cumulatepercenttable, [@cumulatepercent] = _closestpercentage ),
        Data[FW Engineers]
    )
RETURN
    IF ( _currentengineer = _resultengineer, _currentengineer )

[Anonymous]

Thank you very much !!!

[amitchandak]

@Anonymous , follow the blog https://exceleratorbi.com.au/pareto-analysis-in-power-bi/

, in the last use  formula

you can use that id in conditional formatting measure to color the bar

Last Engineer less than <= 80% =
     CALCULATE(
         max(Table[FW engineer]),
         FILTER(Table, [Cumulative %] <= 0.8)
     )

How to do conditional formatting by measure and apply it on pie?
https://www.youtube.com/watch?v=RqBb5eBf_I4&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L
https://community.powerbi.com/t5/Community-Blog/Power-BI-Conditional-formatting-the-Pie-Visual/ba-p/1682539