Forum Discussion

Jerry-P4P's avatar
Jerry-P4P
Frequent Visitor
4 years ago
Solved

SUMX/Calculate Table on data range

Good day community,   I am trying to add some values from different colums into a total value. Sadly I don't seem to get it to work..       In the screenshot you can see what I am trying ...
  • PaulOlding's avatar
    PaulOlding
    4 years ago

    Hi Jerry-P4P 

    I'd recommend unpivotting the data in Power Query. 

    That will get you a table with a single Date column (which you currently have as separate columns) with a corresponding Amount column.

    Your measure is then

    Total Amount = SUM(Table[Amount])
  • Whitewater100's avatar
    Whitewater100
    4 years ago

    Hi What Paul said is good.

    One minor add on is you will want to click the top of each text column in PQ then choose for the drop down "Unpivot Other Columns"

    OR

    Highlight all those data columns only and "Unpivot Columns". Then you just have to sum up one column to get your total as  suggested. 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Jerry-P4P,

We do not so recommend you to store the date values in columns, a table with too many columns should affect the performance.

Optimization guide for Power BI - Power BI | Microsoft Docs
In addition, power bi is not suitable to directly calculate across multiple columns.

For this scenario, I'd like to suggest you refer to the following link to 'unpivot column' the date fields, then you can simply calculate and summary these field values.

Unpivot columns (Power Query) (microsoft.com)

Regards,

Xiaoxin Sheng