statistics per tool per site

[gilles13600]

Hello,

i would like to do a measure for the percentage of tool version that are in official version  per site per tool

i added a colum to get 1 if tool is official one and 0 if not and then i tried somehow to do a measure, but i m turning around and probably miss a filter or something in my formula.

__LAST_GPC_EQUAL_OFFICIAL_GPC = IF(T_SOFT_REPOSITORY[TOOL]="GPC",IF(T_SOFT_REPOSITORY[VERSION]=LOOKUPVALUE(Mail_Tool_Version[Version],Mail_Tool_Version[Tool],"GPC"),1,0))

__GPC = SUM(T_SOFT_REPOSITORY[__LAST_GPC_EQUAL_OFFICIAL_GPC])/CALCULATE(SUM(T_SOFT_REPOSITORY[__LAST_GPC_EQUAL_OFFICIAL_GPC]),T_SOFT_REPOSITORY[TOOL]="GPC")

Here the data:

SITE1	SERVER1	Tool1	5.2.0.3	12/10/2022
SITE1	SERVER1	Tool1	5.2.0.3	13/10/2022
SITE1	SERVER1	Tool1	5.2.0.3	14/10/2022
SITE1	SERVER1	Tool1	5.2.0.3	15/10/2022
SITE1	SERVER1	Tool1	5.2.0.3	17/10/2022
SITE1	SERVER1	Tool1	5.2.0.3	18/10/2022
SITE1	SERVER2	Tool1	5.2.0.3	12/10/2022
SITE1	SERVER2	Tool1	5.2.0.3	13/10/2022
SITE1	SERVER2	Tool1	5.2.0.3	14/10/2022
SITE1	SERVER2	Tool1	5.2.0.3	15/10/2022
SITE1	SERVER2	Tool1	5.2.0.3	17/10/2022
SITE1	SERVER2	Tool1	5.2.0.3	18/10/2022
SITE1	SERVER3	Tool1	5.2.0.4	12/10/2022
SITE2	SERVER4	Tool1	5.2.0.4	18/10/2022
SITE2	SERVER4	Tool2	1.0	18/10/2022

In example above, for SITE1, tool1, i would like to have 33% as 1 server is in 5.2.0.4 version that is official one. Bother server 1 and server 2 have old version

Thank you for your help!

gilles

[v-easonf-msft · Accepted solution]

Hi, @gilles13600 

Please try formula like:

% =
VAR a =
    CALCULATE (
        DISTINCTCOUNT ( T_SOFT_REPOSITORY[Server] ),
        FILTER (
            T_SOFT_REPOSITORY,
            T_SOFT_REPOSITORY[__LAST_Tool1_EQUAL_OFFICIAL_Tool1] = 1
                && T_SOFT_REPOSITORY[Site] = "SITE1"
        )
    )
VAR b =
    CALCULATE (
        DISTINCTCOUNT ( T_SOFT_REPOSITORY[Server] ),
        FILTER ( T_SOFT_REPOSITORY, T_SOFT_REPOSITORY[Site] = "SITE1" )
    )
RETURN
    a / b

Best Regards,
Community Support Team _ Eason

[gilles13600]

thank you, i will try this !

gilles