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.
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:
Solved! Go to Solution.
Hi,
Write this calculated column formula (not measure)
=calculate(sum(data[revenue]),filter(data,data[supplier]=earlier(data[supplier])))
Hope this helps.
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.
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.
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/
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |