Forum Discussion

adnanarain's avatar
adnanarain
Icon for Helper V rankHelper V
6 years ago
Solved

Difference of two rows for same column in matrix visual

I am attaching sample data and the desired output. with this data, I am using matrix visual to find number of accounts, Difference, and %. Number of accounts is a simple  count([account Number]) and the difference for row 0 (i.e Date Difference Column) will always be 0 and Row 1 is Account numbers of Row 0 - account numbers of Row 1, in example Account numbers of Row 0 = 3 and Account numbers of Row 1 = 1 so the difference for Row 1 will be 3-1 = 2 and last I want to calculate % for Row 0 the % will be 0 and for Row 1 the formula for % is difference (i.e 2 for Row 1) divided by count of account number for Row 0 (i.e 3) so 2/3 = 67%.

Below is the sample data:

Date Difference        Account Number              Account Open date
012013
022013
032014
022013
032014
032014
142014
152013
162014
272013
282014
292013
3102013
3112014
4122013
4132013
4142014
4152014

 

and below is the desired output, I tried to show for Year 2013 only:

 

 2013                             
Date Difference      Count of account        Difference         %
0300%
11267%
22-1-100%
31150%
42-1-100%

I tried to explain, apologies in advance if anything is not clear.

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    HI adnanarain ,

     

     

    Create these measures

     

     

    Count of Accounts = Count('Table'[Account Number])

     

     

     

    Difference = 
var a = CALCULATE([Count of Accounts] , FILTER(ALL('Table'),'Table'[Account Open date] = MAX('Table'[Account Open date]) && 'Table'[Date Difference] = MAX('Table'[Date Difference]) - 1))

RETURN

IF (ISBLANK(a), 0, a - [Count of Accounts])

     

     

     

    Percentage = 
var a = CALCULATE([Count of Accounts] , FILTER(ALL('Table'),'Table'[Account Open date] = MAX('Table'[Account Open date]) && 'Table'[Date Difference] = MAX('Table'[Date Difference]) - 1))
RETURN
DIVIDE([Difference],a,0)

     

     

     

     


    Regards,

    Harsh Nathani


    Appreciate with a Kudos!! (Click the Thumbs Up Button)

    Did I answer your question? Mark my post as a solution!

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    HI adnanarain ,

     

     

    Create these measures

     

     

    Count of Accounts = Count('Table'[Account Number])

     

     

     

    Difference = 
var a = CALCULATE([Count of Accounts] , FILTER(ALL('Table'),'Table'[Account Open date] = MAX('Table'[Account Open date]) && 'Table'[Date Difference] = MAX('Table'[Date Difference]) - 1))

RETURN

IF (ISBLANK(a), 0, a - [Count of Accounts])

     

     

     

    Percentage = 
var a = CALCULATE([Count of Accounts] , FILTER(ALL('Table'),'Table'[Account Open date] = MAX('Table'[Account Open date]) && 'Table'[Date Difference] = MAX('Table'[Date Difference]) - 1))
RETURN
DIVIDE([Difference],a,0)

     

     

     

     


    Regards,

    Harsh Nathani


    Appreciate with a Kudos!! (Click the Thumbs Up Button)

    Did I answer your question? Mark my post as a solution!