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,
I have 5 columns: Date, Cost, Month, Revenue, Product
My dataset looks like this :
Date | Cost | Month | Revenue | Product |
2018-01 | 10 | 1 | 20 | A |
2018-01 | 10 | 2 | 30 | A |
2018-01 | 25 | 1 | 50 | B |
2018-01 | 25 | 2 | 100 | B |
2018-02 | 5 | 1 | 15 | A |
2018-02 | 5 | 2 | 25 | A |
2018-02 | 30 | 1 | 30 | B |
2018-02 | 30 | 2 | 60 | B |
I would like to create a calculated column that sums the cost for all products by Month.
For example in 2018-01, the cost should be 35 and 35 as well for 2018-02.
My final out should look like this:
MATRIX
Date | Cost | 1 | 2 |
2018-01 | 35 | 70 | 130 |
2018-02 | 35 | 45 | 85 |
Making a measure for this is simple but since we can't put a measure in the "Rows" of a matrix I need to make this as a calculated column
So I am thinking that in order to achieve this I need in my matrix fields:
Rows: Date and Cost (new column)
Columns: Month
Values: Revenue (Summed aggregation
How can I achieve this ?
Solved! Go to Solution.
Please try this expression in your column. I called your example table "Cost", so replace that with the actual table name.
Cost All =
CALCULATE ( SUM ( Cost[Cost] ), ALLEXCEPT ( Cost, Cost[Date], Cost[Month] ) )
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Please try this expression in your column. I called your example table "Cost", so replace that with the actual table name.
Cost All =
CALCULATE ( SUM ( Cost[Cost] ), ALLEXCEPT ( Cost, Cost[Date], Cost[Month] ) )
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Using the formula works on my dummy pbi.
However, for some reason , it does not work in my actual report. I know im not giving you much info here but could there be a reason why the aggregations doesn't work? It giving me different Cost for Each Month. It should be the same regardless of the Month values. It should be aggregated on the basis of the Date column
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 |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
107 | |
105 | |
87 | |
61 |