Hi everyone,
I'm new to Power BI and trying to build a new table (table) from the bigger one (initial_table) based on some calculations and filtering. I couldn't find a solution to my problem myself. My table is quite big but in general it looks similarly as in this example:
| Country | Source | Sum |
| Austria | Internal | 23 |
| Austria | External | 18 |
| Austria | Other | 99 |
| France | External | 44 |
| France | Internal | 57 |
| Italy | Internal | 11 |
To create it, I use the following DAX command: table = SUMMARIZECOLUMNS(initial_table[Country],initial_table[Source],"Sum",[Sum]), where Sum metric is the sum of prices per country and per source calculated as Sum = CALCULATE(SUM(initial_table[Price]),ALLEXCEPT(initial_table,initial_table[Source],new_table[Country]))
What I would like to do is to split column "Source" into separate columns reflecting data for the underlying sources per country (where the fields with no data should be replaced with the blank field), i.e. I want the final tabel to be the following:
| Country | Internal | External | Other |
| Austria | 23 | 18 | 99 |
| France | 57 | 44 | |
| Italy | 11 | | |
I tried creating the metric which should reflect such selection for each of the columns and then assign a new column to that metric, for instance for Internal column it would be the following two lines respectively:
Internal_metric = IF(SELECTEDVALUE(table[Source])="Internal",[Sum],BLANK())
Internal = Internal_metric
However, the values in my new column do not make sense - the sum becomes the sum of prices for all countries, not just for one particular country I am looking at. Also, even if they were to be correct, if I remove Source and Sum columns (which I need to do in the end), the data dissapears. One more complication is that it's not possible to use pivoting in the query editor because I used DAX to create the table. How can I solve that?
Thanks for any help,
Zhanna
