Table with an explicit total column

[dbobonec]

Hi,

I'm struggling with the following problem: I need to create a table in Power BI Desktop that give me a measure value (let's say capital) by ContractType and Bank, as the example below, but I also need a specific column with the sum of all the bank values (column AllBanks). 

The point is I cannot use the Total out-of-the-box feature because when I export this data to a csv totals are not included (well known limitation, not yet solved by Microsoft) and I need to be to export all data in the table.

ContractType	BankA	BankB	BankC	AllBanks
TypeA	100	200	300	600
TypeB	200	300	400	900
TypeC	300	400	500	1200

Can anyone help me with this topic please?

Thanks,

Diogo

[v-easonf-msft]

Hi， @dbobonec 

If you want to export the column subtotal in matix through "export data" in PowerBI Desktop, I am afraid that there is no corresponding solution yet.

You need to manually create an actual Total measure and apply it to the value of your matrix.

All Banks = CALCULATE(SUM('Table'[value]),ALLEXCEPT('Table','Table'[Bank]))

And you will find the total in the csv file you exported.

If I misunderstood, please let me know.

Best Regards,
Community Support Team _ Eason

[dbobonec · Accepted solution]

Hi @v-easonf-msft,

Thank you very much for your reply, for what I see from your pbix it will work for my example.
I need to adapt it to my real problem, with higher complexity, but I will accept it as a solution!

Thank you and regards, Diogo 

[mahoneypat]

Is your data unpivoted (i.e., all bank names in same column)?  That is the recommended format but it can be done either way.  Do you plan to export from a matrix visual?  or are you going to copy out of data view or the query editor?

Regards,

Pat

[dbobonec]

Hi mahoneypat,

Thank you for your reply.
Yes, all bank names are in the same column.

My goal is to export to a csv file from a matrix visual, using «export data» option.

Regards,

Diogo