Forum Discussion
Sum by Value in other Column
Hi,
I have a dataset that looks like this and I am trying to do the following:
I want to have the sum of all Values in (Column D) when Column B = 'Income 1' e.g. for a certain ID-Number 1001
In other words I want to have the sum of all the values in Column D for all Incomes (Column B) by Column A (ID-Number)
The funny thing is that visually Power BI does this automatically (When i visualize the table in Power BI it automatically aggregates all the values for a certain income and groups them by ID-Number, so I can see the correct sums visually but I cannot calculate anything to get what power BI does automatically.
| Column A (ID-Number) | Column B (Income) | Column C (Month) | Column D (Value) |
| 1001 | Income 1 | 1 | 100 |
| 1001 | Income 2 | 1 | 10 |
| 1001 | Income 3 | 1 | 20 |
| 1001 | Income 1 | 2 | 30 |
| 1001 | Income 2 | 2 | 25 |
| 1001 | Income 3 | 2 | 20 |
| 1002 | Income 1 | 1 | 200 |
| 1002 | Income 2 | 1 | 10 |
| 1002 | Income 3 | 1 | 15 |
| 1002 | Income 4 | 1 | 5 |
| 1002 | Income 1 | 2 | 15 |
Hi gregor,
Then you add [income] column in ALLEXCEPT function.changed_Column = CALCULATE(SUM(Test1[Column D (Value)]),ALLEXCEPT(Test1,Test1[Column A (ID-Number)],Test1[Column B (Income)]))
Please see the following screenshot.
Best Regards,
Angelia
6 Replies
When PowerBI calculates this for you and seperates it in the visual, it is only using the SUM function.
Total Income= SUM([Value])
If you create that simple measure, it will use the rows or axis to define the filter context (aka Income Type).
If you need specific calculations for one, you would wrap the expression inside Calculate and specify the context of the filter.
Total Income 1 1001= CALCULATE([Total Income], [Income]="Income1 ", [ID-Number]=1001)
Total Income 2 1001= CALCULATE([Total Income], [Income]="Income 2", [ID-Number]=1001)
Total Income 3 1001 = CALCULATE([Total Income], [Income]="Income 3", [ID-Number]=1001)
Total Income 1 1002= CALCULATE([Total Income], [Income]="Income1 ", [ID-Number]=1002)
Of course, table and column names are assumed based upon the inmatiforon provided.
Hi kcantor
thank you for your response.
But Power BI also splits up the values by the ID since I put in ID as a field in my visual (so it also calculates the Sum per ID since I selected ID to show in my visual)
Power BI shows it like this
ID Income Value
10001 Income 1 10.000 (which is the sum of all values for this ID and Income 1) - this is what I am trying to generate
I would need this for all types of Income (there are 500 types of income in my database) and all different IDs (there are around 600 different IDs)
In your solution I could get the Sum of one income for one ID, what I would need however is the sum of all types of income for all different IDs
Is there a way to replace [Income]="Income1" with just [Income] and it will give the value if Income as Output and calculate the sum?
Thank you
Hi gregor,
What visual do you create?
>>In your solution I could get the Sum of one income for one ID, what I would need however is the sum of all types of income for all different IDs
You can use ALLEXCEPT function, if you just group the value sum for different ID, just use ALLEXCEPT(Test1,Test1[Column A (ID-Number)]).Column = CALCULATE(SUM(Test1[Column D (Value)]),ALLEXCEPT(Test1,Test1[Column A (ID-Number)]))
If you have any question, please let me know.
Best Regards,
Angelia
