Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi Guys,
I want to create a measure to subtract and add the columns from the tables with no direct relationship, see the screenshot below.
After some search on the internet, I created the below measure, BUT it doesn't work properly when I use it with the column 'Sales Invoice'[Aging Bucket]. It shows the $ values way too big.
Balance Due =
VAR __IN_Invoice_Amnt = SUM('Sales Invoice'[Ivoice Amnt])
VAR __IN_Balance = SUM('Incoming Payment'[IN_Balance])
VAR __CN_Balance = SUM('Credit Memo'[CM_Balance])
VAR __DT_Balance = SUM('Down Payment'[DP_Balance])
RETURN
((__IN_Balance_Due - __RC_OpenBalSc) - __CN_Open_Balance) + __DT_Balance_Due
Thank you,
Mohammad
@Mohammadwazeri I guess first question would be why you can't create those relationships. But, you should be able to do something like this:
VAR __PBCustomers = DISTINCT('Customer'[PBCustomerKey])
VAR __Invoices = SUMX(FILTER('Sales Invoice'), [PBCustomerKey] IN __PBCustomers),[Invoice Amt])
Hi @Greg_Deckler ,
I am not sure, I am following it.
Would you mind writing the whole calculation?
Thanks,
Mohammad
Balance Due =
VAR PBCustomers = DISTINCT('Customer'[PBCustomerKey])
VAR Customers = DISTINCT('Customer'[Customer_Key])
VAR IN_Invoice_Amt = SUMX(FILTER('Sales Invoice'), [PBCustomerKey] IN PBCustomers),[Invoice Amt])
VAR IN_Balance = SUMX(FILTER('Incoming Payment', [Customer_Key] IN Customers), [IN_Balance])
VAR CN_Balance = SUMX(FILTER('Credit Memo', [PBCustomerKey] IN PBCustomers),[CM_Balance])
VAR DT_Balance = SUMX(FILTER('Down Payment', [PBCustomerKey] IN PBCustomers),[DP_Balance])
RETURN
((IN_Balance_Due - RC_OpenBalSc) - CN_Open_Balance) + DT_Balance_Due
That said, this line in your code doesn't seem to correspond to known VARs
((IN_Balance_Due - RC_OpenBalSc) - CN_Open_Balance) + DT_Balance_Due
Thank you @Greg_Deckler for your quick reply!
I tried to use the "Balance Due" with the 'Sales Invoice'[Aging Bucket], but now it is adding every values for every aging bucket this is the main problem.
User | Count |
---|---|
92 | |
85 | |
77 | |
66 | |
62 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
59 |