Skip to main content
cancel
Showing results for 
Search instead 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

Reply
OffermansWE
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. 

OffermansWE_0-1718784302735.png

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:
OffermansWE_1-1718784570441.png
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):
 OffermansWE_3-1718785302396.png

 

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

Thank you in advance!

 

 

1 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. 

View solution in original post

3 REPLIES 3
rajendraongole1
Community Champion
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!
Appreciate your Kudos!!

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. 

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. 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.