topic Re: Calculate Weightfactor based on row count of two tables in DAX Commands and Tips

Calculate Weightfactor based on row count of two tables

OffermansWE — Wed, 19 Jun 2024 08:24:50 GMT

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!

Re: Calculate Weightfactor based on row count of two tables

rajendraongole1 — Wed, 19 Jun 2024 09:30:48 GMT

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!!

Re: Calculate Weightfactor based on row count of two tables

OffermansWE — Thu, 20 Jun 2024 06:09:48 GMT

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=83idmeh4l7l4gdwofjbv5ntt5&st=rbnkmaqh&dl=0) 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.

Re: Calculate Weightfactor based on row count of two tables

OffermansWE — Fri, 21 Jun 2024 10:27:04 GMT

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.