Forum Discussion
Sumup Column based on Filter as Calculated Column
Hi Guys, Can someone help to get the formula in a calculated Column. (I derived it in Measure, but i need the same calculation in Calculated Column).
Measure Formula: Current Month Total = CALCULATE( SUM('Table1'[Month 1]), 'Table1'[Class] IN { "A" } )
| Class | Month 1 | Month 2 | Month 3 |
| A | 10 | 20 | 22 |
| B | 12 | 13 | 123 |
| A | 13 | 14 | 12 |
hi Anonymous
Do you want show this expected output in power bi calculated table?
If so, you need to create a new table like below:
New table = SUMMARIZE(Table1,Table1[Subject],"Month 1",CALCULATE(SUM(Table1[Month1]),FILTER(Table1,Table1[Class]="A")))Result:
here is sample pbix file, please try it.
Regards,
Lin
6 Replies
- amitchandak
Super User
Create a column like
Current Month = if('Table1'[Class] IN { "A" },'Table1'[Month 1],blank() )
and choose Agg sum when use.
But I not clear with data model you posted.
In case you are looking for slicer to give you which measure then refer
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedinamitchandak this doesnt gives the correct value.., All i need is to filter "A" in Class and get the Month1 data for all A in calculated Column. Below is my exact version of data which i loaded in Power BI.
Subject Class Month1 Maths A 10 Maths B 13 Maths A 11 Computer A 24 The value which i expect is in Power BI Table is:
Subject Month 1 Maths 21 Computer 24 it just filter only "A" and provide the value against it.
Measure Formula which works: Current Month Total = CALCULATE( SUM('Table1'[Month 1]), 'Table1'[Class] IN { "A" } )
and same formula i need in Calcluated Column...
v-lili6-msftCommunity Support
hi Anonymous
Do you want show this expected output in power bi calculated table?
If so, you need to create a new table like below:
New table = SUMMARIZE(Table1,Table1[Subject],"Month 1",CALCULATE(SUM(Table1[Month1]),FILTER(Table1,Table1[Class]="A")))Result:
here is sample pbix file, please try it.
Regards,
Lin
- v-lili6-msft
hi Anonymous
Just adjust the formula as below:
CALCULATE( SUM('Table1'[Month 1]), FILTER(ALL(Table1),'Table1'[Class] IN { "A" } ))Regards,Linv-lili6-msft this formula which you provided sums all i guess.., i am not getting the value only for that particular selection. 😞
