Forum Discussion

sam_nik12345's avatar
sam_nik12345
Helper II
4 years ago
Solved

row-wise multiplication

My table looks like below: product fact Jan'18 Feb'18 Mar'18 b 1.5 2 3 4   I want add another row to achieve like below: q   SUM(Jan'18) *fact SUM(Jan'18) *fact SUM(Jan'18...
Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi sam_nik12345 ,

    By my test, add a row is not supported by DAX, you can add a calculated column instead, here's my solution.

    1.This is my sample.

    2.In Power Query, select all columns at the same time, then click Unpivot Columns tab under the Transform ribbon.

    Get this result.

    3.Click Use First Row as Headers tab under the Transform ribbon, and rename the Product column.

    Get this result.

    4.Create a measure.

    P =
IF (
    MAX ( 'Table'[Date] ) = "Fact",
    BLANK (),
    MAX ( 'Table'[b] )
        * MAXX ( FILTER ( ALL ( 'Table' ), 'Table'[Date] = "Fact" ), 'Table'[b] )
)

    Get the result.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

amitchandak's avatar
amitchandak
Super User
4 years ago

sam_nik12345 , Can you share raw data(table) to check and how actually want to display.

 

We might append the results like

 

 

example

union(
summarize('Table',"Measure","sales","This period",[SALES YTD],"Last period",[SALES LYTD],"POP",[SALES YOY])
summarize('Table',"Measure","unit","This period",[unit YTD],"Last period",[unit LYTD],"POP",[unit YOY])
)

 

 

or


union(
summarize('Table','Table'[country],"Revenue","sales","This period",[Revenue YTD],"Last period",[Revenue LYTD],"POP",[Revenue YOY])
summarize('Table','Table'[country],"Costs","unit","This period",[Costs YTD],"Last period",[Costs LYTD],"POP",[Costs YOY])
)

sam_nik12345's avatar
sam_nik12345
Helper II
4 years ago

Hi amitchandak 

I want to create a row, multiplying the fact with every month.

Jan'18 *fact, Feb'18*fact, Mar'18*fact