row-wise multiplication

[sam_nik12345]

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) *fact

How can I achieve this? Kindly help.

[v-yanjiang-msft · Accepted solution]

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]

@sam_nik12345 , Assume month is row in you data, you can try a measure like

Prd= PRODUCTX(Values('Date'[Month Year]), [Measure])

You can push this grand total using isinscope

if(not(isinscope('Date'[Month]), [Prd],[Measure] )

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

[sam_nik12345]

Hi @amitchandak , i think this has something to do with insertrow dax function?

[amitchandak]

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

Hi @amitchandak 

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

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