Forum Discussion

gilles13600's avatar
gilles13600
New Member
3 years ago
Solved

statistics per tool per site

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:

SITE1SERVER1Tool15.2.0.312/10/2022
SITE1SERVER1Tool15.2.0.313/10/2022
SITE1SERVER1Tool15.2.0.314/10/2022
SITE1SERVER1Tool15.2.0.315/10/2022
SITE1SERVER1Tool15.2.0.317/10/2022
SITE1SERVER1Tool15.2.0.318/10/2022
SITE1SERVER2Tool15.2.0.312/10/2022
SITE1SERVER2Tool15.2.0.313/10/2022
SITE1SERVER2Tool15.2.0.314/10/2022
SITE1SERVER2Tool15.2.0.315/10/2022
SITE1SERVER2Tool15.2.0.317/10/2022
SITE1SERVER2Tool15.2.0.318/10/2022
SITE1SERVER3Tool15.2.0.412/10/2022
SITE2SERVER4Tool15.2.0.418/10/2022
SITE2SERVER4Tool21.018/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

Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    3 years ago

    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

2 Replies

  • v-easonf-msft's avatar
    v-easonf-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    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