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
gscara
Frequent Visitor

Need Assistance to Calculate Sum of Table Field Filtered By Another Table

I am trying to develop a model for a company using a Binary Tree model.  Each affiliate is uniquely located on a Binary Tree and  I am calculating performance metrics for affiliates, their children, and subsequent generations.  The intent is to be able to model dynamic changes to the system.

 

The relationship diagram shows an Affiliates table, which links to the User#InFamily of the FamilyTreeGen1 table.  This creates a relationship between each affiliate and their first generation children.  The FamilyTreeGen1 table then links to the FamilyUnderChildren table.  This table shows all the subsequent generations that are related to the children.  This table is connected to the sales data through an intermediary table UsezFamilyGen2Plus that has unique values to ensure that the the connection can be made to the SalesFamily table.  This removes the many to many conflict.Relationships.JPGI have been butting my head for several days as I need to calculate a ratio.  For each affiliate, I want to calculate the following ration for each of their first generation children  =  sum(business-volume of each child and their descendants)/Sum(business-volume of all of their children and their descendants.

 

The next diagram shows a summary table which identifies the two users that are first generation children of a single affiliate.  The calculation TotAfil%Total = Divide(sum(SalesFamily[Business Volume]),calculate(sumx(SalesFamily,SalesFamily[Business Volume])))   This formula, and all variations that I have created either calculate 1 or zero for these two rations.  I am looking for 1800/2860 = 0.63 and 1060/2860 = 0,37  

FamilyFirstGeneration.JPG

I want to be able to filter the SalesFamily table so that I can get a total sales for an affiliate's children and their descendants (these are the users in FamilyTreeGen1 plus FamilyUnderChildren).

 

Any and all help would be appreciated!  Thanks in advance.

2 REPLIES 2
v-chuncz-msft
Community Support
Community Support

@gscara,

 

You may try ALL Function.

TotAfil%Total =
DIVIDE (
    SUM ( SalesFamily[Business Volume] ),
    CALCULATE ( SUM ( SalesFamily[Business Volume] ), ALL ( SalesFamily ) )
)

 

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you very much for your suggestions.  I was not aware of the ALL function.  Unfortunately, when I tested the formula it did tTesting-Formula.JPGnot provide the correct output.  I have attached an image of the resulting calculation, and as you can see the totals do not add up to 100%.   It appears that the ALL command calculates a different divisor for each User#, whereas the divisor should be a constant and should equal the Total value for the Business Volume column.

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.