Forum Discussion
Power BI - Conditional sum column
There is a table with the structure below where I have Revenue for months 1 thru 12 for Account....... (Rev1, Rev2...Account, are different columns)
I'd like to Add Rev1 numbers for only accounts "ABC" and "DEF". So I want to see a Total field 15 for Accounts ABC and DEF. Please advise the best way to acheive it.
Rev1 Rev2 Rev3.....Rev12 Account
10 15 12 14 ABC
5 6 9 11 DEF
4 15 7 9 GHI
32 19 1 12 XYZ
VS_5 -
In addition to Unpivoting your fact table, I would add additional dimension tables for Account and Date. This will allow you to add meaningful groupings and other descriptive attributes. For example, your account table could begin with all of the distinct accounts, and then add a column for "Account Category", which could be used to put accounts into buckets.
I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.
4 Replies
VS_5 -
The most straightforward solution is to use "Unpivot" in Power Query. This will produce 3 columns: Account, Month, and Revenue.
Then, in your report, you can add a slicer or filter to limit the accounts.
I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.Thank you for taking time to respond to my query. I like the idea of Unpivoting the columns. As my actual table is huge and I'm trying to convert excel report to Power BI, I will have to use formulas like adding 2 or 3 cells with the condition on more than 2 Accounts (say Add Rev2 cost for accounts ABC, DEF and GHI).
So what is the best way to specify that condition on Accounts?
Again, thanks for your time.
VS_5 -
In addition to Unpivoting your fact table, I would add additional dimension tables for Account and Date. This will allow you to add meaningful groupings and other descriptive attributes. For example, your account table could begin with all of the distinct accounts, and then add a column for "Account Category", which could be used to put accounts into buckets.
I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.
