Measure - Filter / Table / Summarizecolumns help needed

[fredf64600]

Hello

I struggle on trying to achieve the following behavior in coding the right formula in a Measure of my PBI

Example - Table is

Market	Country	Type	Value
BNL	NL	Xmas	3
BNL	BE	Xmas	4
BNL	LU	Xmas	5
FR	FR	Xmas	6
BNL	NL	Sales	7
BNL	BE	Sales	8
BNL	LU	Sales	9
FR	FR	Sales	10

I have this formula (Measure)

"Nb Countries Distinct selected" = COUNTROWS(SUMMARIZECOLUMNS(Table[Country], FILTER(Table, Table[Type] = "Xmas")))

(user will filter the report by Market > for each selection = find the number of distinct countries for Xmas whetever the Type selected = always retrieve this figure and then use it in a measure (whatever Type value selected) = sum (Value) / Nb distinct countries (for Xmas type)

It gave good result but when I when to use this calculated measure into another measure (to get the sum Value / nb countries distinct selected) = failed.

I tried other stuff but cannot find a way to do so. Any idea ?

[johnt75 · Accepted solution]

You can't use SUMMARIZECOLUMNS in a measure, where you need that functionality you have to use ADDCOLUMNS( SUMMARIZE()), but in this case it isn't necessary. Try

Nb Countries Distinct selected =
COUNTROWS (
    CALCULATETABLE (
        DISTINCT ( 'Table'[Country] ),
        REMOVEFILTERS (),
        'Table'[Type] = "Xmas"
    )
)

[fredf64600]

Great ! Just tweaked removing the Removed Filters but works perfectly !