Forum Discussion

abukapsoun's avatar
abukapsoun
Post Patron
8 years ago
Solved

Calculated Table

Hi Dears,   I have the following Table I have the following formula Count X = CALCULATE(COUNT('table1'[Value1]),FILTER(table1','table1'[Value1]="X"))   How can I create a new table, that return...
Need Help
  • malagari's avatar
    malagari
    8 years ago

    This probably isn't the cleanest way (I was trying to do it in one DAX expression initially), but you can create a summary table and then add a calculated column on top of it.

     

    First step would be to summarize your data table by Period and Count of X.  I went ahead and added an "Index" column - this assumes that your periods will always be incrementing (P1, P2, ... P5, P6, .. Pn).

     

    SummaryTable = SUMMARIZE(
                      Table1,
                      Table1[Period],
                      "Index", MID( Table1[Period], 2, LEN( MAX(Table1[Period]) ) ),
                      "Count of X", CALCULATE( COUNTROWS(Table1), FILTER(Table1, Table1[Value] = "X"))
                   )

    This will give us a table that looks like:

     

     

    From here, we can add a "Difference" column using the following formula:

     

    Difference = [Count of X] - LOOKUPVALUE('Table'[Count of X], 'Table'[Index], 'Table'[Index] + 1)

     

    Note: If you receive an error on this last step, you need to change the Data Type for the [Index] column to a Whole Number instead of Text.

abukapsoun's avatar
abukapsoun
Post Patron
8 years ago

malagari

Thanks for your support, In fact I want to generate 2 columns, 

Period             Difference

P1                    Count of X in P1 - Count of X in P2

P2                    Count of X in P2 - Count of X in P3

P3                    Count of X in P4 - Count of X in P5

P4                    Count of X in P5 - Count of X in P6

 

I want to generate how much we had an increment in X count between the periods. 

 

malagari's avatar
malagari
Continued Contributor
8 years ago

This probably isn't the cleanest way (I was trying to do it in one DAX expression initially), but you can create a summary table and then add a calculated column on top of it.

 

First step would be to summarize your data table by Period and Count of X.  I went ahead and added an "Index" column - this assumes that your periods will always be incrementing (P1, P2, ... P5, P6, .. Pn).

 

SummaryTable = SUMMARIZE(
                  Table1,
                  Table1[Period],
                  "Index", MID( Table1[Period], 2, LEN( MAX(Table1[Period]) ) ),
                  "Count of X", CALCULATE( COUNTROWS(Table1), FILTER(Table1, Table1[Value] = "X"))
               )

This will give us a table that looks like:

 

 

From here, we can add a "Difference" column using the following formula:

 

Difference = [Count of X] - LOOKUPVALUE('Table'[Count of X], 'Table'[Index], 'Table'[Index] + 1)

 

Note: If you receive an error on this last step, you need to change the Data Type for the [Index] column to a Whole Number instead of Text.

  • abukapsoun's avatar
    abukapsoun
    Post Patron
    8 years ago

    malagari

     

    Thank you! It was exactly what I need.

    One last thing please, what is the logic of that lockupvalue function?