Calculating % of total sales by function and country

[MARS_5676]

Hi,

Can somebody help me please 🙂

I'm trying to calculate SG&A % of sales by function for every country but I'm not able to calculate it correctly. I'm using:

SG&A/Sales = IF(ISINSCOPE(Master[Cost Center Group]),DIVIDE(Master[SG&A],CALCULATE(Master[Sales],ALL(Master[Cost Center Group]))))

The data looks something like this. So I'm calculating Sales and SG&A, then when I try to divide each function SG&A by total of sales it's not working.

Cost Center Function	Country	Account Category	Value
F one	Italy	Sales	1222
F two	Italy	SG&A	12
F three	France	Cost to Serve	23

[v-eqin-msft · Accepted solution]

Hi @MARS_5676 ,

I have created a data sample:

So according to this: I'm trying to calculate SG&A % of sales by function for every country 

If the desired output is ——for Italy: = 12/ 1222  ;  for France: =15/ 67 ,please try:

Measure = 
var _sga=CALCULATE(SUM('Table'[Value]),FILTER('Table',[Country]=MAX('Table'[Country]) && [Account Category]="SG&A"))
var _all=CALCULATE(SUM('Table'[Value]),FILTER('Table',[Country]=MAX('Table'[Country]) && [Account Category]="Sales"))
RETURN DIVIDE(_sga,_all)

If you are still confused about it, please provide me with more details about your problem/expected output to help us clarify your scenario.

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

[amitchandak]

@MARS_5676 , Try a measure like

divide( calculate(sum(Table[Value]), [Account Category] = "SG&A"),calculate(sum(Table[Value]), [Account Category] = "Sales"))

[MARS_5676]

Thank you! 

Unfortunatelly it's not working, I can see only total divided by total for some reason 😞