Forum Discussion

dbobonec's avatar
dbobonec
Frequent Visitor
5 years ago
Solved

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

Need Help
  • dbobonec's avatar
    dbobonec
    5 years ago

    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 

4 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    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's avatar
      dbobonec
      Frequent Visitor
      5 years ago

      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

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    5 years ago

    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's avatar
      dbobonec
      Frequent Visitor
      5 years ago

      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