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 am trying to sum the cost column by StartMonth. The issue is that my cost can be duplicated for a Product if there are multiple End Dates. For example, in row 2 and 3, the cost is just 10$ NOT 20$.
Start End Cost Product
2020-01-01 | 5 | A | |
2020-01-01 | 2020-02-01 | 10 | B |
2020-01-01 | 2020-03-01 | 10 | B |
2020-01-01 | 2020-02-01 | 15 | C |
2020-01-01 | 2020-03-01 | 15 | C |
2020-02-01 | 2020-05-01 | 30 | D |
2020-02-01 | 2020-06-01 | 30 | D |
2020-02-01 | 2020-05-01 | 50 | E |
2020-02-01 | 2020-06-01 | 50 | E |
2020-02-01 | 100 | F |
The final output should look something like this:
Start | Cost |
1/1/2020 | 30 |
2/1/2020 | 180 |
For January: Product A (5$), Product B (10$), Product C (15$)
For February: Product D (30$), Product E (50$), Product F (100$)
I tried this formula and almost works but it's not able to sum the rows with Blank End Dates. How can I make sure it takes them into account?:
Cost By YearMonth =
CALCULATE(
SUM('Fact'[Cost]),
ALLEXCEPT(
'Fact',
'Fact'[Start],
'Fact'[End]
))
Note: I need a calculated column NOT a measure
Solved! Go to Solution.
Here is a column expression that works, although you should consider doing this either as a measure or in the query editor with a Group By step.
Cost by YearMonth =
CALCULATE (
SUMX ( VALUES ( 'Fact'[Product] ), CALCULATE ( AVERAGE ( 'Fact'[Cost] ) ) ),
ALLEXCEPT ( 'Fact', 'Fact'[Start] )
)
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.
Try sumx(summarize(Table, Table[Start],Table[Product],"_1",max(Table[Cost])),[_1])
I need a calculated column
Here is a column expression that works, although you should consider doing this either as a measure or in the query editor with a Group By step.
Cost by YearMonth =
CALCULATE (
SUMX ( VALUES ( 'Fact'[Product] ), CALCULATE ( AVERAGE ( 'Fact'[Cost] ) ) ),
ALLEXCEPT ( 'Fact', 'Fact'[Start] )
)
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.
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 |