Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Hi @Anonymous ,
Don't think about it too complicated, try
Measure = CALCULATE(SUM(DimCountryTable[Sales]),FILTER(ALL(DimCountryTable),DimCountryTable[Sales]>12000))
Best Regards,
Jay
@v-jayw-msft Thanks for your answer.This formula is similar to what I posted in OP and does not work in a model with normalized model.
@Anonymous , Try a measure like
sumx(filter(summarize(Table,DimCountryTable[Country],"_1",sum(Table[sales])),[_1]>12000),[_1])
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |