cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Calculate Weightfactor based on row count of two tables

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.

1 ACCEPTED SOLUTION
Frequent Visitor

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.
3 REPLIES 3
Community Champion

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!

Frequent Visitor

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.

Frequent Visitor

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.