Get percentage of values in Column A, for which there are two values in column B & two in C

[Flawn]

Hello Everyone,

I've hit a wall when trying to produce a fairly complex percentage calculation. The data is confidential so I will provide an example table below.

Client ID	Goal	Circumstance
001	X
001	X
001		X
001		X
001
002	X
002		X
002		X
002		X
003	X
003	X
003	X
003		X
003		X
003		X

So, my dillema is this. I need to calculate the percentage of total DISTINCT Client IDs that have greater than or equal to two non-blank rows in both "goals" AND "circumstances". So for the above table, the result would need to be 66.6~% - counting client 001 once, 003 once and exluding 002 all together.

Happy to provide further info if needed, and thanks in advance for any help you are able to provide!

[tamerj1 · Accepted solution]

Hi @Flawn 
Please refer to attached file with the solution 

% Goal > Circumstance = 
VAR AllSelectedClients = 
    VALUES ( Data[Client ID] )
VAR RequiredClients =
    SUMX (
        AllSelectedClients,
        CALCULATE (
            VAR CountGoal = COUNTROWS ( FILTER ( Data, NOT ISBLANK ( Data[Goal] ) ) )
            VAR CountCircumstance = COUNTROWS ( FILTER ( Data, NOT ISBLANK ( Data[Circumstance] ) ) )
            RETURN
                IF ( CountGoal >= 2 && CountCircumstance >= 2, 1, 0 )
        )
    )
RETURN
    DIVIDE ( RequiredClients, COUNTROWS ( AllSelectedClients ) )

[Flawn]

At a cursory look, this appears to be returning the results we would expect, but i need to do some testing to be sure. Kudos for now, will return and accept as solution once testing is complete if it proves to be accurate.

[MahyarTF]

Hi,

You could create the measure like below :

Goal Perc =

Var _CurrClient = SELECTEDVALUE(Sheet158[ClientID])

Var _PerClientCnt = CALCULATE(count(Sheet158[ClientID]),

                         FILTER(sheet158, Sheet158[ClientID] = _CurrClient)

                        )

Var _GoalTikedClient =  CALCULATE(count(Sheet158[ClientID]),

                         FILTER(sheet158, Sheet158[ClientID] = _CurrClient),

                         FILTER(sheet158, Sheet158[Goal] = "*")

                        )

Var _CircTikedClient =  CALCULATE(count(Sheet158[ClientID]),

                         FILTER(sheet158, Sheet158[ClientID] = _CurrClient),

                         FILTER(sheet158, Sheet158[Circumstance] = "*")

                        )

return if(_GoalTikedClient > 1 && _CircTikedClient > 1, "Yes", "No")

--DIVIDE(_GoalTikedClient, _PerClientCnt)

And then use it in your visual.

* in the particular Measure, there is Count of each ClientId, and Goal and Circumstance ticked one, you could divide those or use each individually to calculate the percentage.

Appreciate your Kudos

[Flawn]

I really like this solution theoretically - I apprecaite how it allows for flexible adjustment via variables - but for whatever reason I cannot get it to work in practice. It always returns all results as "no" and even then, I struggle to actually utilize the variables to construct a functional percentage calculation/division formula. This would be my preferred of the presented solutions if I could get it to work, so for now i'll kudos it in hopes of some fixes!

[Anonymous]

Hi @Flawn , 

Assuming your table looks like this.

Client ID	Goal	Circumstance
1	X
1	X
1		X
1		X
1
2	X
2		X
2		X
2		X
3	X
3	X
3	X
3		X
3		X
3		X

Step 1 : Create a custom table

Table = 
ADDCOLUMNS (
    SUMMARIZE ( Sheet1, Sheet1[Client ID] ),
    "Count of Goal", CALCULATE ( COUNT ( Sheet1[Goal] ), Sheet1[Goal] <> "" ),
    "Count of Circumstances", CALCULATE ( COUNT ( Sheet1[Circumstance] ), Sheet1[Circumstance] <> "" )
)

 
Step 2: In the new table , create a calculated column

% = 
DIVIDE (
    CALCULATE ( SUM ( 'Table'[Count of Goal] ), 'Table'[Count of Goal] >= 2 ),
    COUNT ( 'Table'[Count of Goal] )
)

 
Step 3 : Output

Hope that helps

Dax_Noob