How to create additional column in matrix

[anaxha]

Hi,

I am trying to create new column in matrix, but nothing works so far for me. The result supposed to look like this, I already have Sales Actual and Sales Forecast columns. How can I create % of Sales columns?:

Expected result

[v-yadongf-msft · Accepted solution]

Hi @anaxha ,

This is my test table:

Please add a column called “Profit” in Power Query:

Check Product column, Month column, Acutual/OP column, then Unpivot Other Columns in Transform tab.

You will get a table like below:

Please try following DAX:

% of sales = 
VAR Sumsales = CALCULATE(SUMX(FILTER('table','table'[Attribute]="Sales"),[Value]),ALLSELECTED('table'))
VAR SUMValue = SUM('table'[Value])
VAR percentage = IF(MAX('table'[Attribute])<>"Sales" && MAX('table'[Attribute])<>"Profit",SUMValue/SUMSales,BLANK())
RETURN percentage

Create a table visual and you will get result you want:

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@anaxha , If sales is a value in a table column then

divide([Measure], calculate([Measure], filter(all(Table[KPI]) , Table[KPI] ="Sales"))

but if you have done this using calculation groups, refer I tried a few combinations here. if those can help

Calculation Groups- Measure Slicer, Measure Header Grouping, Measure to dimension conversion. Complex Table display : https://youtu.be/qMNv67P8Go0

[anaxha]

Hi, 

Thank You for the answer. I wasn't precise. My data looks something like this, so in this case I don't have any measures: