Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Visualization with a common denominator

Hi,

 

For people familiar with accounting: I would like to see where do entries from one account go to. As in "other side of a journal entry".

 

Basically the data is this as in the table below:

 

AccountIdentifierSum
1000565100€
110056520€
1200565-120€
1200572-101€
130057250€
140057250€
15005721€
1600577-5000€
16005775000€

 

Of course, there will be hundreds of thousands of lines. But the rule is:

1. All Identifiers (565 for example) equal out to zero in the Sum-field. 

 

Goal:

I would like to create a dashboard, where i can filter account 1200 and it would present to me the following:

 

Account 
1000100€
110020€
130050€
140050€
15001€

 

How do i start to create this? So the function would be like this:

Filter all Identifiers that are in account 1200 (565 & 572 in this case).

If account <> 1200 then SUM the values of these identifiers and show which account they are assigned to

 

I'm finding it hard to find the logic for this.  

 

If someone could guide me to the right direction, i would be most grateful! 

 

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

    Hi Anonymous 

    Create a new table

    Table 2 = SUMMARIZE('table','table'[Account],'table'[Identifier])

    Then create measures

    Measure = CONCATENATEX(VALUES('Table 2'[Identifier]),'Table 2'[Identifier],",")

Measure 2 =
CALCULATE (
    SUM ( 'table'[Sum] ),
    FILTER (
        'table',
        FIND (
            'table'[Identifier],
            [Measure],
            1,
            0
        ) <> 0
            && 'table'[Account]
                <> SELECTEDVALUE ( 'Table 2'[Account] )
    )

     

    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

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Try Like

    measure =
var _value = maxx(allselected(table,table[Account]))
return
sumx(filter(crossjoin(selectcolumns("Acc1",table[Account],"Iden1",table[Identifiertable[,"_sum1",table[Sum]),
					  selectcolumns("Acc2",table[Account],"Iden2",table[Identifiertable[,"_sum2",table[Sum]))
					 , [Iden1] =[Iden2] && [Acc1]<>[Acc2] && [Acc1] = _value),[_sum2])
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi,

       

      Ive been trying to replicate this but i get lost on the selectcolumns -part of the code. What are the "Acc1, Acc2, Iden1 and Iden2" names? Are these created by the code or should i have them. Also the table[Identifiertable seems like something i dont have. 

       

      Basically ive been trying to add this measure to my data. The other solution posted to this thread does work, but it is far to slow. (+20min for my data). I'm hoping this solution might be more efficient. 

       

      Thank you for your assistance! 

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

    Hi Anonymous 

    Create a new table

    Table 2 = SUMMARIZE('table','table'[Account],'table'[Identifier])

    Then create measures

    Measure = CONCATENATEX(VALUES('Table 2'[Identifier]),'Table 2'[Identifier],",")

Measure 2 =
CALCULATE (
    SUM ( 'table'[Sum] ),
    FILTER (
        'table',
        FIND (
            'table'[Identifier],
            [Measure],
            1,
            0
        ) <> 0
            && 'table'[Account]
                <> SELECTEDVALUE ( 'Table 2'[Account] )
    )

     

    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.