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 =DateProductPrice1/1/2022Product A$51/1/2022Product B$82/1/2022Product A$102/1/2022Product B$4 Table2 = SUMMARIZECOLUMNS(Calender[End Of Month],Table1[[Product],"Change", [Monthly%_Change] DateProductChange1/1/2022Product A0%1/1/2022Product B0%2/1/2022Product A100%2/1/2022Product B50%Table3 = SUMMARIZECOLUMNS(Calender[End Of Month],"ProductA%", CALCULATE(MIN('Table2'[Change] ), 'Table2'[Product] = "ProductA"),"ProductB%", CALCULATE(MIN('Table2'[Change]), 'Table2'[Product] = "ProductB"))) DateProduct A%Product B%1/1/20220%0%2/1/2022100%50%

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 - PrevMontPricevar monumber = SELECTEDVALUE(Dates[Month No.])returnIF( NOT(ISBLANK(currprice) && monumber >=2), DIVIDE(pricediff, [Prev Mont Price])) The Generate Table function might help here, not entirely sure...

Forum Discussion

VikrantC

Helper I

4 years ago

Solved

Table Transformation with DAX

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

4 years ago

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
4 years ago
Great VikrantC
If it solved your problem, would you please consider marking my reply as accepted solution?

10 Replies

Whitewater100
Solution Sage
4 years ago
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
  Helper I
  4 years ago
  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
    Solution Sage
    4 years ago
    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
Community Champion
4 years ago
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
  Helper I
  4 years ago
  Awesome, This worked. Thank you very much.
  - tamerj1
    Community Champion
    4 years ago
    Great VikrantC
    If it solved your problem, would you please consider marking my reply as accepted solution?

Forum Discussion

Table Transformation with DAX

10 Replies

Related content

Table Transformation

Table Transformation

Table Transformation

Transform columns / type

Table Transformation