Total of all which have a certain value

[Anonymous]

I am failing on this particular subject which, at first, seemed quite doable to me.

In theory, I have the following table.

Country	Sales
Germany	20000
France	22000
Belgium	8000
The Netherlands	10000
UK	17000
Spain	12000
Italy	14000
Sweden	8500
Norway	6000

I would like to have the total of sales of all countries where sales is > 12000. This would equal in above result to the sum of Germany, France, UK, Italy, so 73000.

I need this to get back in a measure as a fixed value. My model holds seperated facts and dimensions. I am trying this with the following formula:

Calculate(sum(sales) , filter( all(DimCountryTable) , sum(sales) > 12000).

This will simply add up the total sales of all countries but will only show for those countries with sales above 1200. Not what I want.

What I am basically want to achieve is this result if I make a grid. Please keep in mind, a fixed table is not an option for me as slicers still need to keep functioning.

Country	Sales	Sales2
Germany	20000	73000
France	22000	73000
Belgium	8000	73000
The Netherlands	10000	73000
UK	17000	73000
Spain	12000	73000
Italy	14000	73000
Sweden	8500	73000
Norway	6000	73000

Thank you in advance!

[Anonymous]

Hi @Anonymous ,

Don't think about it too complicated, try

Measure = CALCULATE(SUM(DimCountryTable[Sales]),FILTER(ALL(DimCountryTable),DimCountryTable[Sales]>12000))

 

Best Regards,

Jay

[Anonymous]

@Anonymous Thanks for your answer.This formula is similar to what I posted in OP and does not work in a model with normalized model.

[camargos88]

Hi @Anonymous ,

Try this measure:

_Sum_GT12000 = SUMX(SUMMARIZE('Table', 'Table'[Country], "Total", CALCULATE(SUM('Table'[Sales]))), IF( [Total] >=12000, [Total], BLANK()))

[amitchandak]

@Anonymous , Try a measure like

sumx(filter(summarize(Table,DimCountryTable[Country],"_1",sum(Table[sales])),[_1]>12000),[_1])

[Greg_Deckler]

@Anonymous  - I think you can get what you want by using the same technique that is used for measure totals. Basically, create the table you want as a var and then sum and filter across it. 
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907