topic Re: divide 2 measure internal in DAX Commands and Tips

divide 2 measure internal

x0 — Mon, 11 Mar 2024 14:05:29 GMT

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

)

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),

)

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 .

Re: divide 2 measure internal

govindarajan_d — Mon, 11 Mar 2024 18:22:55 GMT

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?

Re: divide 2 measure internal

Anonymous — Tue, 12 Mar 2024 07:13:44 GMT

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.

Re: divide 2 measure internal

x0 — Tue, 12 Mar 2024 08:09:52 GMT

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.

Re: divide 2 measure internal

x0 — Tue, 12 Mar 2024 08:26:24 GMT

Hello Anonymous

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 .

Re: divide 2 measure internal

Anonymous — Tue, 12 Mar 2024 08:49:04 GMT

Hi x0 ,

Please provide relevant test data and describe it.

Best Regards,
Adamk Kong

Re: divide 2 measure internal

x0 — Thu, 21 Mar 2024 12:13:11 GMT

Hello Anonymous

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]
)