divide 2 measure internal

[x0]

Hello All ,

I have created 2 measure which is as per this 

1 is 

Total Names Count Divided by 3 =

VAR SelectedMonths = VALUES('CustomTable'[MonthIndex])

VAR SelectedYears = VALUES('CustomTable'[Year])

VAR SelectedProperties = VALUES('CustomTable'[Property])

RETURN

    DIVIDE(

        COUNTROWS(

            FILTER(

                'Move Insq',

                'Move Insq'[MonthIndex] IN SelectedMonths &&

                'Move Insq'[YearPart] IN SelectedYears &&

                'Move Insq'[Property] IN SelectedProperties

            )

        ),

        3

    )

and 2 is this 

Total Move insq percent =

VAR LeasingAgentsWithCustomData =

    FILTER(

        'Move Insq',

        'Move Insq'[MonthIndex] IN VALUES('CustomTable'[MonthIndex]) &&

        'Move Insq'[YearPart] IN VALUES('CustomTable'[Year]) &&

        'Move Insq'[Property] IN VALUES('CustomTable'[Property]) &&

        'Move Insq'[First Name] <> BLANK()

    )

RETURN

    DIVIDE(

        COUNTROWS(LeasingAgentsWithCustomData),

        3

    )

now i want to create 3rd measure which is simple do divide 1 measure / 2nd measure 

unfortunatlly 

i have created this 

Final Measure1 =

VAR Ratio = DIVIDE(

    [Total Names Count Divided by 3],

    [Total Move insq percent]

)

RETURN

    IF(

        ISBLANK(Ratio),

        BLANK(),

        Ratio

    ) but dosent showcorrect value showing 100 % 

help me if anyone have any simple idea or simple way to achive this calculation . 

[v-kongfanf-msft]

Hi @x0 ,

Please provide relevant test data and describe it.

Best Regards,
Adamk Kong

[v-kongfanf-msft]

Hi @x0 ,

Based on the formula you provided, it looks like you want to divide the first measurement by the second measurement. I have tried the following modification, please refer to it:

Total Move insq percent =
VAR LeasingAgentsWithCustomData =
    FILTER (
        'Move Insq',
        'Move Insq'[MonthIndex]
            IN VALUES ( 'CustomTable'[MonthIndex] )
                && 'Move Insq'[YearPart]
                    IN VALUES ( 'CustomTable'[Year] )
                        && 'Move Insq'[Property]
                            IN VALUES ( 'CustomTable'[Property] )
                                && 'Move Insq'[First Name] <> BLANK ()
    )
RETURN
    DIVIDE ( COUNTROWS ( LeasingAgentsWithCustomData ), COUNTROWS ( 'Move Insq' ) )

Total Names Count Divided by 3 =
VAR SelectedMonths =
    VALUES ( 'CustomTable'[MonthIndex] )
VAR SelectedYears =
    VALUES ( 'CustomTable'[Year] )
VAR SelectedProperties =
    VALUES ( 'CustomTable'[Property] )
RETURN
    DIVIDE (
        COUNTROWS (
            FILTER (
                'Move Insq',
                'Move Insq'[MonthIndex]
                    IN SelectedMonths
                        && 'Move Insq'[YearPart]
                            IN SelectedYears
                                && 'Move Insq'[Property]
                                    IN SelectedProperties
                                        && 'Move Insq'[First Name] <> BLANK ()
            )
        ),
        3
    )

Final Measure1 = 
DIVIDE(
    [Total Names Count Divided by 3],
    [Total Move insq percent]
)

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[x0]

Hello @v-kongfanf-msft 

seems like your provided solution work for me 

but i need some modification 

lets look this measure first 

Total Names Count Divided by 3 =
VAR SelectedMonths =
    VALUES ( 'CustomTable'[MonthIndex] )
VAR SelectedYears =
    VALUES ( 'CustomTable'[Year] )
VAR SelectedProperties =
    VALUES ( 'CustomTable'[Property] )
RETURN
    DIVIDE (
        COUNTROWS (
            FILTER (
                'Move Insq',
                'Move Insq'[MonthIndex]
                    IN SelectedMonths
                        && 'Move Insq'[YearPart]
                            IN SelectedYears
                                && 'Move Insq'[Property]
                                    IN SelectedProperties
                                        && 'Move Insq'[First Name] <> BLANK ()
            )
        ),
        3
    )

on this i dont want to divide with 3 i just want when i select multiple divide with that select month for example i selected only 8 month 2023 measure should divide with 1 and if selecte 8+9 2023 should be divide with 2 

now same thing i want for this measure 

Total Move insq percent =
VAR LeasingAgentsWithCustomData =
    FILTER (
        'Move Insq',
        'Move Insq'[MonthIndex]
            IN VALUES ( 'CustomTable'[MonthIndex] )
                && 'Move Insq'[YearPart]
                    IN VALUES ( 'CustomTable'[Year] )
                        && 'Move Insq'[Property]
                            IN VALUES ( 'CustomTable'[Property] )
                                && 'Move Insq'[First Name] <> BLANK ()
    )
RETURN
    DIVIDE ( COUNTROWS ( LeasingAgentsWithCustomData ), COUNTROWS ( 'Move Insq' ) )

 same as per first and the final measure should perfororm 

Final Measure1 = 
DIVIDE(
    [Total Names Count Divided by 3],
    [Total Move insq percent]
)

[x0]

Hello @v-kongfanf-msft 

As per your Exercies 

result appear wrong value 

please have a look ,

Total Move insq Percent showing 0.29 but actual is 14.67 

Final Measure should be  1.67/14.67 = 11%  , 9.33/14.67= 64 % , 3.00/14.67 = 20% , 0.67/14.67 = 5% .

is any issue with my custom table ? here is my custom table 

CustomTable =

FILTER(

    ADDCOLUMNS(

        UNION(

            SELECTCOLUMNS('Conversion', "First Name", UPPER(LEFT('Conversion'[First Name], 1)) & LOWER(RIGHT('Conversion'[First Name], LEN('Conversion'[First Name]) - 1)), "Property", 'Conversion'[Property], "Month1", 'Conversion'[Month1], "Year", 'Conversion'[YearPart], "MonthIndex", 'Conversion'[MonthIndex]),

            SELECTCOLUMNS('Move Insq', "First Name", UPPER(LEFT('Move Insq'[First Name], 1)) & LOWER(RIGHT('Move Insq'[First Name], LEN('Move Insq'[First Name]) - 1)), "Property", 'Move Insq'[Property], "Month1", 'Move Insq'[Month1], "Year", 'Move Insq'[YearPart], "MonthIndex", 'Move Insq'[MonthIndex]),

            SELECTCOLUMNS('J Turner Dashboard', "First Name", UPPER(LEFT('J Turner Dashboard'[First Name], 1)) & LOWER(RIGHT('J Turner Dashboard'[First Name], LEN('J Turner Dashboard'[First Name]) - 1)), "Property", 'J Turner Dashboard'[Property], "Month1", 'J Turner Dashboard'[Month1], "Year", 'J Turner Dashboard'[YearPart], "MonthIndex", 'J Turner Dashboard'[MonthIndex]),

            SELECTCOLUMNS('Online Review', "First Name", BLANK(), "Property", 'Online Review'[Property], "Month1", 'Online Review'[Month1], "Year", 'Online Review'[Year], "MonthIndex", 'Online Review'[MonthIndex]),

            SELECTCOLUMNS('Shop Report Tracker (2)', "First Name", UPPER(LEFT('Shop Report Tracker (2)'[First Name.], 1)) & LOWER(RIGHT('Shop Report Tracker (2)'[First Name.], LEN('Shop Report Tracker (2)'[First Name.]) - 1)), "Property", 'Shop Report Tracker (2)'[Property], "Month1", 'Shop Report Tracker (2)'[Month1], "Year", 'Shop Report Tracker (2)'[Year], "MonthIndex", 'Shop Report Tracker (2)'[MonthIndex])

        ),

        "IsDistinct", IF([First Name] <> BLANK() && [Property] <> BLANK() && [MonthIndex] <> BLANK() && [Year] <> BLANK(), 1, 0)

    ),

    [IsDistinct] = 1

)

as i am filter the property , monthindex , year and First Name from this table. 

and actually this table is showing correct information for other table datas having issue with this move insq table only only this final measure dosent work. or else look perfect. 

let me know if additional inforation do you require. 

Thanks .  

[govindarajan_d]

Hi @x0,

Can you tell me what is your problem in detail? Like I understand you are not getting 100% but what should be the value you are expecting. Can you provide some screenshots that will help?

[x0]

Hello @govindarajan_d 

Now look Below screen shot . 

marked with yellow mark is my final measure which is doing divide Total Names Count Divided by3 / Total Move insq percent this 2 measure i already shared on my train message 

but the final measure appear 100% for all row which totally wrong its should to as per screen shot 

look at the value 

1.67  9.33  3.00  0.67  this all value from my measure Total names Count divided by 3 

and look this values 14.67 this value come from measure 2 which is total move insq percent 

so this is my problem. and actually when you do 1.67/14.67 = 11%  , 9.33/14.67= 64 % , 3.00/14.67 = 20% , 0.67/14.67 = 5% . should be reflect this value but all row showing 100 % 

let me know if need additional information.