Journal Tracing

[Anonymous]

Hi,

I am not sure if this is possible but I cannot seem to wrap my head around it. I currently have a dataset with various columns including document number for which I am trying the report the following. Once a user selects a revenue line or balance, the report should show on what other lines the journals have had an impact.

For example - If I would have three groups (group A: 1, 2, 3 - Group B, 2, 4 & Group C - 3, 5) filtering on Group A should show me the impact it had on Group B (based on transaction 2) and Group C (based on transaction 3).  Currently, applying a filter only shows group A transactions.

Is there any way this can be overcome?

Kind regards,
Rick

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Create a new table, 

Group = SUMMARIZE(Data,Data[Group],Data[Doc Number])

Thne create measures

Measure = CONCATENATEX(VALUES('Group'[Doc Number]),'Group'[Doc Number],",")

Measure 2 = CALCULATE(SUM(Data[Amount]),FILTER(Data,FIND([Doc Number],[Measure],1,0)<>0))

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

Difficult to say, really need some sample data and expected output. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

Hey Greg,

Thank you for your quick reply - I have created some example data and added my expectation of the results. Hope the link below works (standard OneDrive).

Example Data 

Based on the dataset the issue is that I am only able to create option 1 (filter on a specific group and show the related entries therein). However, as the transaction lines hit multiple groups - which in the end should reconcile to zero - I was hoping to be able to get PowerBi to filter create option 2 automatically.

Hope this makes it a bit more clear!

Thank you once again!

Regards,

Rick