Add previous year comparison to table for several metrics

[NKAnalyst]

Hello,

I am trying to create a table like this excel table in PowerBi:

Units sold, Turnover and Market Share all come from different tables, which are connect via a Date and Country table.

I added Country to Column and Units sold, Turnover, Market Share to Value. Afterwards I turned "show on rows" on to have my Metrics shown in rows rather than in columns. No I am stuck with the %change last month column. I created calculated metrics for units sold, turnover, marketshare, however if I pull them in they show up below units sold, turnover, market share.

Any ideas how I can add one column per country which is giving me the %change for each metric?

Any help is highly appreciated!

[NKAnalyst]

Hi @v-kelly-msft  and @amitchandak ,

First of all apologizes for all the questions I am quite a newby to PowerBi.

I think I found out what the problem is: units sold, market share etc are all measures I created and not columns. I messed this up - sorry  

If I move all the measures into on table (i.e 'Dax measures') the first summarize command works. However i dont have the Country dimension in this table there then (since it is coming from another table, i.e 'Dim_country')

If I try the second example:
union(
summarize('Dax measures','Dim_country'[country],"Revenue","sales","This period",[Revenue YTD],"Last period",[Revenue LYTD],"POP",[Revenue YOY])
summarize('Dax measures','Dim_country'[country],,"Costs","unit","This period",[Costs YTD],"Last period",[Costs LYTD],"POP",[Costs YOY])
)
PowerBi does not recognize the country table (I assume because there is no relationship betwenn "Dax measure" an "Dim Country". 

Given the new setting any idea what I could do to get a tabel with all measure split by country?

best regards

[v-kelly-msft · Accepted solution]

Hi  @NKAnalyst ,

Summarize uses the same table,you cant add more tables in the function,you could use Addcolumns instead.

Pls pay attention to the syntax:

https://docs.microsoft.com/en-us/dax/summarize-function-dax

https://docs.microsoft.com/en-us/dax/addcolumns-function-dax

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[amitchandak]

@NKAnalyst , this is measure split to dimension. With all the dimensions you need as the filter you need to create a table 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])
)

[NKAnalyst]

Hi @amitchandak , 

thanks a lot for your reply. I just tried both ways, however unfortunately both dont seem to work in my case.

For the first one I get the following error message: "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value." and for the second one I get: "The column 'Country' specified in the 'SUMMARIZE' function was not found in the input table." Guess this is because my measures are stored in a different table than my country names. If so is there a workaround?

[v-kelly-msft]

Hi  @NKAnalyst ,

Can you share your .pbix file for test?

Remember to remove the confidential information.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[NKAnalyst]

Hi @v-kelly-msft , unfortunately not since it contains confidential data (thats why I created excel tables with random data). any other idea for degubbig ?

Best regards