Total of a measure without row context ?

[olivierpkz]

Hello !

I have a problem in a DAX formula to retrieve the total of a measure and use it in another measure.

I explain the context :

I have a salary table with 1 row by employee / month, and each salary belong to a category.

I need to display the number of man / woman by category, and then calculate a measure using the total number of employees :

I begin to make a matrix summarized by Category in row and Gender in columns, like this :

	Number Man	Number Woman	Total
Category A	4	6	10
Category B	3	2	5
Category C	7	5	12
Category D	8	3	11
TOTAL	22	16	38

The problem is when I try to add a measure to this table in which I have to retrieve the total number of employees (38), it always keep the context of my matrix and calculate the number by category.

I try all the solutions I found on the forum (using ALL, ALLEXCEPT, ISFILTERED) without success.

The measure must be calculate for each category but in my DAX formula I just need to use 38, for example in the line Category A I must calculate a ponderate coefficient : 10/38

It seems obvious but I admit I fail to do this...

If someone knows a solution, it will be great, thank you !

[v-eachen-msft · Accepted solution]

Hi @olivierpkz ,

I created a sample table like yours. The “Number Man” and “Number Woman” measures calculated the counts of man or woman. The “Total” are the sum of the previous two measures. Now, you need to create a measure to get the total value as a coefficient.

TotalDiv =
[Total] & "/"
    & SUMX ( ALL ( 'Table' ), 'Table'[Number] )

"SUMX ( ALL ( 'Table' ), 'Table'[Number] )" is the code you want.

Best Regards,

Eads

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

[olivierpkz]

Hello and thank you for your help !

Your measure keep the context of the matrix rows, it doesn't show the total I want :

Here is the measure : 

Effectif Test = SUMX(ALL('Salariés - Salariés');[FH - Effectif])

And the result :

I need to calculate 36 for each row...

Any other ideas please ?

[olivierpkz]

@v-eachen-msftany ideas how to do it ? Thank you very much !

[RobbeVL]

Hi There,

I think the easiest in this context would be to add a calculated column.
Add a column "Category" with IF salary < 1000 ; "Category A", ...
Then just drag a Category to columns  and you'll have your table.

Hope this helps.

Robbe