Add column to matrix in Power BI

1
votes

I am working with Power BI Desktop and I need to add a column to a matrix in order to calculate totals without adding to each column.

This is my original table:

Original table

I have two calculated measures: [Overdue Balance] and [Expiring Balance] that together results in the measure [Accounting Balance] (in the previous image is Total).

I'd like to add both calcuated measures as totals in the previous table (only as totals, not as column values).

Desired table

Any ideas to achieve this?

Thanks in advance.

1
powerbidaxpowerquery
I don't think it's possible in your current layout. What you can do instead: create separate measures for each age bucket and for the totals, and put them into the matrix. – RADO

1 Answers

0
votes

After a lot of web digging we achieved this by doing the following:

  1. I created two tables with one bucket each other like: Created Tables
  2. I added two conditional columns with each bucket to the accounting balance table according to the calculated measure [Expiration days]:

Expiring bucket:

  • If [Expiration days] is greater or equal to 0 then value is 1.
  • If [Expiration days] is greater than 30 then value is 2.
  • ...
  • If [Expiration days] is greater than 180 then value is 7.
  • Otherwise is 8.

Then I did the same for the Overdue bucket column:

  • If [Expiration days] is less to 0 then value is 1.
  • If [Expiration days] is less than -30 then value is 2.
  • ...
  • If [Expiration days] is less than -180 then value is 7.
  • Otherwise is 8.

Therefore a record would have both columns like:

  • ID: whatever
  • Expiration days: 24
  • Expiring bucket: 1
  • Overdue bucket: 8
  1. I made the relation from the accounting balance table to both created tables
  2. Then I made the tables and they were sorted like this: Resulting table