Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 th...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    Hi,

    Write this calculated column formula (not measure)

    =calculate(sum(data[revenue]),filter(data,data[supplier]=earlier(data[supplier])))

    Hope this helps.

MattAllington's avatar
MattAllington
Community Champion
5 years ago

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/