Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Journal Tracing

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

Need Help
Show and Tell
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    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.

3 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    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.
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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