cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
abukapsoun Member
Member

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 in Column1 the Period (P1, P2, ...) and Column2, the value of (Count X P3 - Count X P2)

so P3 will be (Count X P3 - Count X P2) and so on 

 

1.PNG

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
malagari Member
Member

Re: Calculated Table

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:

 

Screen Shot 2017-09-22 at 11.26.39 AM.png

 

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)

Screen Shot 2017-09-22 at 11.29.08 AM.png

 

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.

Dan Malagari
Consultant at Headspring
4 REPLIES 4
malagari Member
Member

Re: Calculated Table

Can you show what you want the resulting table to look like? I'm having a hard time following your explanation.

 

It sounds like your case would be a good use of the SUMMARIZE function.  You can group by the Period, and then define your calculated columns with whatever formula you please (this is where I was lost in your explanation).

 

Screen Shot 2017-09-22 at 10.35.42 AM.png

 

Dan Malagari
Consultant at Headspring
abukapsoun Member
Member

Re: Calculated Table

@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 Member
Member

Re: Calculated Table

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:

 

Screen Shot 2017-09-22 at 11.26.39 AM.png

 

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)

Screen Shot 2017-09-22 at 11.29.08 AM.png

 

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.

Dan Malagari
Consultant at Headspring
abukapsoun Member
Member

Re: Calculated Table

@malagari

 

Thank you! It was exactly what I need.

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

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 180 members 2,077 guests
Please welcome our newest community members: