Table Transformation with DAX

[VikrantC]

I have a Table (Table1) with date, product and price columns.  I calculated Monthly change as Table2 using a measure (Monthly%_Change) and created a new column called "Change".

I used the Table2 to create a new table (Table3) for columns for "productA" and "ProductB".   I want to use the Table3 and I do not have any use for Table2.  How do I write the DAX formula to create Table3? 

Table 1 =

Date	Product	Price
1/1/2022	Product A	$5
1/1/2022	Product B	$8
2/1/2022	Product A	$10
2/1/2022	Product B	$4

Table2 =

SUMMARIZECOLUMNS(Calender[End Of Month],

Table1[[Product],

"Change", [Monthly%_Change]

 

Date	Product	Change
1/1/2022	Product A	0%
1/1/2022	Product B	0%
2/1/2022	Product A	100%
2/1/2022	Product B	50%

Table3 =

SUMMARIZECOLUMNS(Calender[End Of Month],

"ProductA%", CALCULATE(MIN('Table2'[Change] ), 'Table2'[Product] = "ProductA"),

"ProductB%", CALCULATE(MIN('Table2'[Change]), 'Table2'[Product] = "ProductB")))

 

Date	Product A%	Product B%
1/1/2022	0%	0%
2/1/2022	100%	50%

[Whitewater100 · Accepted solution]

Hi Vikrant:

Off the top of my head I can't see a way to do that, (I'm sure someone knows). Generally, the when the data model is  set up with fact and dim tables it allows for easier DAX and more complex analysis at the same time. Staying in one table can only take you so far.

Sorry if this doesn't do the table answer you'd like.

Here is one measure that produces the same results:(with the Date Table in Model)

MOM % Avg Price 2 =

var monthavg = AVERAGEX(VALUES(Dates[Month]),

average(TableA[Price])

)

var PrevMontPrice = CALCULATE([Monthly Avg], PREVIOUSMONTH(Dates[Date]))

var currprice = SELECTEDVALUE(TableA[Price])

var pricediff = monthavg - PrevMontPrice

var monumber = SELECTEDVALUE(Dates[Month No.])

return

IF( NOT(ISBLANK(currprice) && monumber >=2),

DIVIDE(pricediff, [Prev Mont Price]))

The Generate Table function might help here, not entirely sure...

[tamerj1 · Accepted solution]

Hi @VikrantC 

You may try

Table3 =
ADDCOLUMNS (
    SUMMARIZE ( Table1, Calender[End Of Month] ),
    "Product A%", CALCULATE ( [Monthly%_Change], Table1[Product] = "Product A" ),
    "Product B%", CALCULATE ( [Monthly%_Change], Table1[Product] = "Product B" )
)

[tamerj1 · Accepted solution]

Hi @VikrantC 

You may try

Table3 =
ADDCOLUMNS (
    SUMMARIZE ( Table1, Calender[End Of Month] ),
    "Product A%", CALCULATE ( [Monthly%_Change], Table1[Product] = "Product A" ),
    "Product B%", CALCULATE ( [Monthly%_Change], Table1[Product] = "Product B" )
)

[VikrantC]

Awesome,  This worked.  Thank you very much.

[tamerj1 · Accepted solution]

Great @VikrantC 

If it solved your problem, would you please consider marking my reply as accepted solution?

[Whitewater100]

Hi VC & TJ:

Tamerj answered correctly and I beleive I mistakenly received credit. Not sure how to ammend that. Thank you. FYI.

[Whitewater100]

Hi:

Please see attached link.  I included a couple extra DAX formulas that show the build and included a Date Table to optimize these types of calculations.

https://drive.google.com/file/d/1DfLQlZaYxSzRJxnjljCwPu1MxvyRzFwV/view?usp=sharing 

[VikrantC]

Hi,

I am able to do this.  However, I want to create a virtual Table3 so that I can use in other formulas.  Currently, I am taking the Table1 and I used a measure to created another table (Table2) to crate percentage.  I am transposing it in Table3.  Basically, I am trying to get to Table3 without creating Table2.  If you can please help it will be nice.  I  can transpose the columns but I trying to transpose with a calculation (measure).  Thanks.  

[Whitewater100 · Accepted solution]

Hi Vikrant:

Off the top of my head I can't see a way to do that, (I'm sure someone knows). Generally, the when the data model is  set up with fact and dim tables it allows for easier DAX and more complex analysis at the same time. Staying in one table can only take you so far.

Sorry if this doesn't do the table answer you'd like.

Here is one measure that produces the same results:(with the Date Table in Model)

MOM % Avg Price 2 =

var monthavg = AVERAGEX(VALUES(Dates[Month]),

average(TableA[Price])

)

var PrevMontPrice = CALCULATE([Monthly Avg], PREVIOUSMONTH(Dates[Date]))

var currprice = SELECTEDVALUE(TableA[Price])

var pricediff = monthavg - PrevMontPrice

var monumber = SELECTEDVALUE(Dates[Month No.])

return

IF( NOT(ISBLANK(currprice) && monumber >=2),

DIVIDE(pricediff, [Prev Mont Price]))

The Generate Table function might help here, not entirely sure...

[VikrantC]

Hi Whitewhater100,

Thank you very much.  Unfortunately, it did not work.

Regards,

[Whitewater100]

Hi:

I checked the measure and it is working. I have added a table with the measure. You can fliter out months as needed or maybe you want to see the months for future calculations.https://drive.google.com/file/d/1POgyUikMWl4Oywy75Z1v9QCs8lbUC-DY/view?usp=sharing 

I hope this is closer.

[VikrantC]

Hi Whitewater100,

Thank you fro trying.  Basically, your results are same as my Table2.  What I am trying to do is not create the table2 but directly create Table3.  In my original post the Table3 is basically there are three columns:  Date, productA and ProductB.  The rows are MOM%.  I can do the price but NOT the MOM%.

Again, thanks for you trying and helping.

Regards,

VC