Ratio including a sum with a subtotal

[Anonymous]

Hi,

I have difficulties to calculate ratio including a sum with a subtotal

Based on the example below (table "customer") I need to create a matrix table with Path and x/y as rows and calulated results in columns

Path	x/y	Step	Nb Cus	Inc	Color
A	x	1		34	Blue
A	y	1		91	Black
B	x	2		83	Red
B	y	2		61	Green
C	x	2		75	White
C	y	2		71	Purple
D	x	2		99	Black
D	y	2		84	Red
A	x	1	66		Blue
A	y	1	32		Black
B	x	2	12		Red
B	y	2	22		Green
C	x	2	79		White
C	y	2	97		Purple
D	x	2	33		Black
D	y	2	26		Red

First and seconds columns are the sum for number of customer and income. No problem here

For the other calculations, I need to divide the total (sum Inc) by the sum NbCus when step = 1. For now I only have results where path equals A but I need to use the results (66+32) for other Path as well

I will also need to be able to filters(by color and other variables available)  in my report and this matrix

I think I need to use the allexcept function but have difficulties to understand how. Any suggestion ?

[Anonymous]

 Thanks for the answer but this solution does not select the sum(Nb cus) where step = 1 and apply this results as divider for any Path, x/y and step combination. The provided answer only works for the first 2 rows but not after. For example for the third row, I need to be able to create the formula 83/(66+32). Did I miss something?

[Anonymous]

Works fine with 2 measures:

ZZM_SumNBCust = CALCULATE(SUM(data[Nb Cus]), ALL(data), data[Step] = 1")

ZZM_SumIncDivided = SUM(data[Inc]) / [ZZM_SumNBCust]

My problem now is that those results will not be able to be modified if I select values from different slicers. Any idea how to take slicers results into account with those calculation ?

[v-chuncz-msft]

@Anonymous ,

Just try to add a measure.

Measure =
DIVIDE ( SUM ( Table1[Inc] ), SUM ( Table1[Nb Cus] ) )