Forum Discussion
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) *fact |
How can I achieve this? Kindly help.
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 _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
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/
Hi amitchandak , i think this has something to do with insertrow dax function?
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])
)
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 _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
