Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Difference between two rows if there is one column the same

Hi all,   I'm trying to create an overview from our accounting system with the outstanding items of our debtors. For this we have a view that has already collected all data. So now I actually hav...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi  

    Please try this measure.

    Filter = 
VAR _FatNum =CALCULATETABLE( VALUES('Table'[OspFactuurNummer]),FILTER('Table','Table'[OspOpenstaand] = 0))
Return
IF(MAX('Table'[OspFactuurNummer]) in _FatNum,0,1)

    Build a matrix as your screenshot , add this measure into filter field and set this measure to show items when value = 1.

    Result is as below.

    Best Regards,

    Rico Zhou

     

    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
5 years ago

Hi Anonymous 

Does RowA and RowB two rows in the same table?

My Sample:

Measure = 
VAR _Count = COUNTAX(FILTER(ALL('Table'),'Table'[Invoice number] = MAX('Table'[Invoice number])),'Table'[Invoice number])
Return
CALCULATE(DISTINCTCOUNT('Table'[Invoice number]),FILTER(ALL('Table'),_Count>=2&& 'Table'[Date] = DATE(2020,12,31)&&'Table'[Diary]<>"start balance"))

Result :

If this reply still couldn't help you solve your problem, please share a sample with me.

 

Best Regards,

Rico Zhou

 

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
5 years ago

Hi Anonymous ,

 

First off: thank you for your reply!

 

These rows are indeed in the same Table.

So I want to create a overview with all of the 'open invoices' (amount, not count).

But i'm getting an error when i'm using your Measure.

I do think that i made a mistake in replacing some of the tables.

Here is a short example of the database:

OspFactuurNummer ; invoice number

OspFactuurBedrag: invoice amount

OspBetaaldBedrag: payed amount

OspOpenstaand: open amount

 

So the result I would like to see that is 

Invoice:

1703562 - payed (not showing in chart)

1703563 - open (showing in chart)

1703564 - open (showing in chart)

1703565 - open (showing in chart)

I can't add a pbix or xlsx file, how can i share a example? Or just images:

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi  

    Please try this measure.

    Filter = 
VAR _FatNum =CALCULATETABLE( VALUES('Table'[OspFactuurNummer]),FILTER('Table','Table'[OspOpenstaand] = 0))
Return
IF(MAX('Table'[OspFactuurNummer]) in _FatNum,0,1)

    Build a matrix as your screenshot , add this measure into filter field and set this measure to show items when value = 1.

    Result is as below.

    Best Regards,

    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.