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
murillocosta
Helper I
Helper I

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

 

 

murillocosta_0-1672753244542.png

 

Thanks

 

1 ACCEPTED SOLUTION
v-tangjie-msft
Community Support
Community Support

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])

vtangjiemsft_0-1672825475380.png

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. 

View solution in original post

5 REPLIES 5
v-tangjie-msft
Community Support
Community Support

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])

vtangjiemsft_0-1672825475380.png

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. 

shalinderverma
Resolver II
Resolver II

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

murillocosta_3-1672784974861.png

 

TOTAL CREDIT FOUND

murillocosta_4-1672784994622.png

 

 

TOTAL BALANCE

murillocosta_2-1672784960467.png

TOTAL BALANCE S (is another attempt which didn't work as well)

murillocosta_6-1672785086333.png

 

 

PBI RESULT: 

murillocosta_0-1672784925036.png

 

CORRECT:

murillocosta_1-1672784940924.png

 

 

 

 

 

 

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

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.

Top Solution Authors