Remove partial filter context in Calculated column

[Anonymous]

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: 

CALCULATE( Sum(Revenue], ALL( Tablename[Country] ) ).

This formula still gives revenue split countrywide but I want accumulated revenue for each vendor to repeat in rows(where that vendor is present) as shown in the screenshot shared.

Can anyone please help me here?

Thank you

Sankalp

[Ashish_Mathur · Accepted solution]

Hi,

Write this calculated column formula (not measure)

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

Hope this helps.

[jostnachs]

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

[Anonymous]

@Ashish_Mathur 

Thanks a lot Ashish. This one formula really helps.

Regards,

Sankalp

[Ashish_Mathur]

You are welome.

[V-lianl-msft]

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.

[MattAllington]

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/