Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
abuttigieg
Regular Visitor

DAX Cross Table Row Calculation help

I have the following table setup:

pbi relationships.PNG

 

We have a weighted calcualtion where we want to apply the percetages to get portion values. So for an event we want to know the weighted count, where weighted count is SUM(1 * [Project].[Project %] * [Project Business Group][Busness Group %] * [Region]. [Region %])

Some points to consider:

  • As they are % values, the calculation needs to be applied at the row level, then summed up
  • There is a Many to Many relationship between [Project] to [Business Group] and [Project] to [Region]
    • i.e. A single project can have multiples of both

I am having a hard time crafting the right DAX expression to achive the weighted counts.

 

 

Any help would be greatly appreciated.

 

Andrew

2 REPLIES 2
v-shex-msft
Community Support
Community Support

Hi @abuttigieg,

 

Based on your relationship, these tables connect with one to many relationships. It is impossible calculate on row level and sum up these values.(you can't accurate mapping these records at row level)

 

In my opinion, you need to summary multiple records first, then calculate on these summarized value.

So your formula should be similar as '1 * SUM([Project].[Project %]) * SUM([Project Business Group][Busness Group %]) * SUM([Region]. [Region %])' (these not the accuracy formula)

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

That logic won't work as it summs the percentages, then multiplies them.

 

I didn't think it was possible anyway. In our olap version of this data, we used multiple fact tables. I may have to implment a similar solution.

 

 

Thanks for looking into this problem for me

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.