Forum Discussion
Remove partial filter context in Calculated column
Hi Folks,
I want to build a data model as shown below:
I want a calculated column "Revenue ignoring country" to show total revenue for each supplier(ignoring country-wise split).
How can this be achieved. Currently, I am using the DAC formula:
Hi,
Write this calculated column formula (not measure)
=calculate(sum(data[revenue]),filter(data,data[supplier]=earlier(data[supplier])))
Hope this helps.
6 Replies
Hi,
Write this calculated column formula (not measure)
=calculate(sum(data[revenue]),filter(data,data[supplier]=earlier(data[supplier])))
Hope this helps.
You are welome.
I have same issue but two different tables. couldnt acheive it though. any help please Re: Adding a calculated colum with different aggre... - Microsoft Fabric Community
The general problem I believe you are having is that what you have shown is not a data model, it is a table. They are not the same thing. A data model is the collection of tables, relationships and formulas that work together to provide for your reporting needs. This is what you should do
1. Create 3 tables. The one you have, a dimension table for supplier and another for country
2. Join the tables on the supplier and country
3. Hide the columns for supplier and country in your current table
4. Create a matrix that looks like your table in the OP, with supplier and country coming from the dimension tables.
5. Then write a measure (not a column) Rev ignoring country.
CALCULATE( Sum(dataTable[Revenue]), ALL( dimTableCountry[Country] ) ).https://exceleratorbi.com.au/the-optimal-shape-for-power-pivot-data/
Hi Anonymous ,
Try:
CALCULATE( Sum(Tablename[Revenue]), ALLEXCEPT(Tablename, Tablename[Country] ) )
You could use the ALLEXCEPT function instead of the ALL function
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
