Forum Discussion
HEADER AND DETAILS ROW DIFFERENT
Hi
Would someone be able to help me with the below? I'm sending the file to be easier to understand
FILE
I have to fact tables
- Customer Debt - which is customer document transactions
- Customer Credit - which are customer credit transactions
They are joined through transaction_id (1:n)
My goal is to create a matrix table visual per customer with a Total Balance measure
Total Balance = Total Invoice - Total Credit
Total Invoice = Total transaction from Fact table Customer Debt
Total Credit = the total credit that the customer has which is being obtained from the "Total Credit Found" measure
PROBLEM:
- Sometimes the header is calculated correctly and the details incorrect
- Sometimes the details are calculated correctly and the header not
For example
1- I have customer C10444 - Child 1 which should be showing a Total balance of $238.98 instead of $566.50 (Although the total header is calculating correctly using $238.98)
2- To the second customer C31975 - Child 2- which is the opposite the row level is showing the correct balance (invoice - credit), but the total header is calculating $49.130.47 while the correct total is $29,804.72
Thanks
Hi murillocosta ,
According to your requirements description, one of the four measures you created "Total Credit Found" in your visual matrix is displayed correctly in some Customers, and some Customers are displayed incorrectly, according to my local testing and research here, it is indeed because of your measure "Total Credit Found" The Switch() function applied to causes problems caused by changes in the logic of the total operation in the current context, and based on my tests based on the .pbix file you provided, you can try to create a new measure like this:
Total Credit Found2 = SUMX( VALUES('fCustomerDebt'[TRANSACTION_NUMBER]) , [Total Credit Found])Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
Hi murillocosta ,
According to your requirements description, one of the four measures you created "Total Credit Found" in your visual matrix is displayed correctly in some Customers, and some Customers are displayed incorrectly, according to my local testing and research here, it is indeed because of your measure "Total Credit Found" The Switch() function applied to causes problems caused by changes in the logic of the total operation in the current context, and based on my tests based on the .pbix file you provided, you can try to create a new measure like this:
Total Credit Found2 = SUMX( VALUES('fCustomerDebt'[TRANSACTION_NUMBER]) , [Total Credit Found])Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks mate!! That's exactly what I was looking for.
I am not able to download your file due to firewall isssues at my end. But I suspect you have some problem with the Customer Dimension or it's relationship with the other fact tables. Are you defining Customer Dimension as a separate table or is it a part of one of the Fact tables. If it is a part of one of the fact tables, then which table? Try to make it as a separate dimension and use the Customer name and Customer Parent Name fields from the Customer Dimension.
Hi Shalinder, thanks for you message.
I tried including Customers as dimesion it worked for the first customerbut the others is still incorrect 😞 . I suspect that might be the way I am calculating the measure "Total Credit Found" as I am using SWITCH not sure if that would have any influence
New Link: https://1drv.ms/u/s!AtCiLfFdr1MmpigsnSJibxL9eDLF?e=QF2EFR
TOTAL INVOICE
TOTAL CREDIT FOUND
TOTAL BALANCE
TOTAL BALANCE S (is another attempt which didn't work as well)
PBI RESULT:
CORRECT:
I can not see the underlying table for Credit Amount. Any specific reason you have a complex calculation embedded in DAX? It seems like due to the filter context the end result is getting affected.
Can you share the data structure for Credit table and the logic (in plain english) for computing the "Total Invoice Found".
