Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
105 | |
79 | |
71 | |
66 |
User | Count |
---|---|
141 | |
107 | |
100 | |
82 | |
74 |