topic Re: HEADER AND DETAILS ROW DIFFERENT in DAX Commands and Tips

HEADER AND DETAILS ROW DIFFERENT

murillocosta — Tue, 03 Jan 2023 13:47:32 GMT

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

Re: HEADER AND DETAILS ROW DIFFERENT

Anonymous — Tue, 03 Jan 2023 15:09:45 GMT

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.

Re: HEADER AND DETAILS ROW DIFFERENT

murillocosta — Tue, 03 Jan 2023 22:31:36 GMT

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:

Re: HEADER AND DETAILS ROW DIFFERENT

Anonymous — Wed, 04 Jan 2023 09:44:45 GMT

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.

Re: HEADER AND DETAILS ROW DIFFERENT

Anonymous — Wed, 04 Jan 2023 14:46:07 GMT

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".

Re: HEADER AND DETAILS ROW DIFFERENT

murillocosta — Wed, 04 Jan 2023 22:07:49 GMT

Thanks mate!! That's exactly what I was looking for.