cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Table with an explicit total column

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.

 

ContractTypeBankABankBBankCAllBanks
TypeA100200300600
TypeB200300400900
TypeC3004005001200

 

Can anyone help me with this topic please?

 

Thanks,

Diogo

1 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 

View solution in original post

4 REPLIES 4
Community Support
Community Support

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.

46.png

If I misunderstood, please let me know.

 

 

Best Regards,
Community Support Team _ Eason

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 

View solution in original post

Super User IV
Super User IV

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors