Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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!
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
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!
@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])
)
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?
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!
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
User | Count |
---|---|
96 | |
87 | |
78 | |
72 | |
68 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |