how to divide two measures, both having different group by

[hoshang]

Hi,

Please go through the below scenario. I have made two measures:

1). LenSelGrp = CALCULATE([MV_Leads], GROUPBY(PL_BAU,PL_BAU[Lender_Selected]))

2). LenShoGrp = CALCULATE([MV_Leads], GROUPBY(PL_BAU,PL_BAU[Lender_Shown]))

Now I am dividing the above two as:

-- LenSelBYLenSho = DIVIDE([LenSelGrp],[LenShoGrp])

 

and obviously i am not getting the desired output as I think maybe it is due to different group by. For example i have same values right now in the image above for right and left table under the Lender_Shown and Lender_Selected columns, so the division for the first row must be = 276/1784 but instead it is showing 1.

-- Both Lender_Selected and Lender_Shown has same categorical values but Lender_Shown column has blanks and Lender_Selected column doesn't have blanks.

-- I need the division as per the Lender_Selected column. How to divide values for each individual row? What is the correct way of doing this having different group by measures?

Thank you.

[lbendlin]

please show more details, for example your data model and the measure definitions.

Please provide sanitized sample data that fully covers your issue. https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[hoshang]

Hi, Thank you for the response.

> Data Model: it is pretty simple and straightforward with no relationships. I have done all the required changes in SQL only and have imported data through stored procedure in Power BI.

> Measure definitions:

1) LenShoGrp = CALCULATE([MV_Leads], GROUPBY(PL_BAU,PL_BAU[Lender_Shown]))

2) LenSelGrp = CALCULATE([MV_Leads], GROUPBY(PL_BAU,PL_BAU[Lender_Selected]))

3) LenSelBYLenSho = DIVIDE([LenSelGrp],[LenShoGrp])

> Sample data:

Lender_Shown		LenShoGrp
L1		1784
L2		6804
L3		28474
L4		928

Lender_Selected		LenSelGrp
L1		276
L2		1903
L3		5332
L4		264

Expected output:-

Lender_Selected		LenSelBYLenSho
L1		276/1784
L2		1903/6804
L3		5332/28474
L4		264/928

As per the expected output above, is the measure "LenSelBYLenSho" correctly defined or else what should be its correct definition?

[lbendlin]

You may want also mention the definition for [MV_Leads].

Note that GROUPBY is not normally used as a filter modifier. It may be ineffective in your case.

Please provide sanitized sample data that fully covers your issue.

[hoshang]

MV_Leads = CALCULATE(DISTINCTCOUNT(PL_BAU[admin_id]), PL_BAU[ismobile_verified] = 1)

-- admin_id column consists of unique integer IDs.

-- ismobile_verified column consists of only two values 0 and 1.