Calculate Weightfactor based on row count of two tables

[OffermansWE]

Hello, 

I am trying to find the weightfactor by dividing the row count of one table by the row count of another table. For this I have two tables. The first table is called BAG. Within this table I have three columns named pandid, builbjr_min (groepen) and dom_functie. The original table is very large, but I added a measure called wf_som_gebr_jaar to count the amount of rows for the table. See the DAX measure below the table. 

wf_som_gebr_jaar_panden = CALCULATE (

    DISTINCTCOUNTNOBLANK( BAG_panden[pandid]) ,

    ALLEXCEPT (

        'BAG_panden',

        BAG_panden[bjr_min (groepen)],

        BAG_panden[dom_functie]

    )

)

I also have another table called EP. For this i have done the same, see table below and dax formula:

wf_som_gebr_jaar_ep_online = calculate(COUNTROWS(EP_online),ALLEXCEPT(EP_online, EP_online[ConstructionYear (groepen)],EP_online[BuildingApplication], EP_online[Code]))

Now I want to create a new measure in which I divide wf_som_gebr_jaar_panden by wf_som_gebr_jaar_ep_online. However, just dividing the two measures does not seem to work. As a result, I would like a table in the report which looks like this (only with the correct values):

 

If anything is unclear, please let me know and I will try to elaborate. 

Thank you in advance!

[rajendraongole1]

Hi @OffermansWE -create a measure that calculates the weight factor by dividing the row count of the BAG_panden table by the row count of the EP_online table

wf_som_gebr_jaar_panden =
CALCULATE(
DISTINCTCOUNTNOBLANK(BAG_panden[pandid]),
ALLEXCEPT(
BAG_panden,
BAG_panden[bjr_min (groepen)],
BAG_panden[dom_functie]
)
)

Create another measure for EP

wf_som_gebr_jaar_ep_online =
CALCULATE(
COUNTROWS(EP_online),
ALLEXCEPT(
EP_online,
EP_online[ConstructionYear (groepen)],
EP_online[BuildingApplication],
EP_online[Code]
)
)

Then create measure to calculate percentage from both above measures.

%weightfactor =  [wf_som_gebr_jaar_panden]/[wf_som_gebr_jaar_ep_online] 

use divide function.

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[OffermansWE]

Hi @rajendraongole1. Unfortunatly this gives the same result as I already had. I have created an example pbix (https://www.dropbox.com/scl/fi/98zjfbgqmls2cqi7z9rdj/Example_file.pbix?rlkey=83idmeh4l7l4gdwofjbv5nt...) of the results and I have added the expected results. Also you can see how the tables are related within this pbix. I actually want to divide the first table by the second table to get the right results in the third table. 

[OffermansWE · Accepted solution]

I have solved the problem by replacing the measure of wf_som_gebr_jaar_panden by the measure: 

wf_som_gebr_jaar_panden =

var _seleted_year = SELECTEDVALUE(EP_online[ConstructionYear (groepen)])

var _selected_application = SELECTEDVALUE(EP_online[BuildingApplication])

var _calculated_total = calculate(DISTINCTCOUNTNOBLANK(BAG_panden[pandid]),BAG_panden[bjr_min (groepen)]=_seleted_year && BAG_panden[dom_functie] = _selected_application)

return _calculated_total

By saving the selected value and filtering the required table based on that value, the correct division is made.