Need to add "Percentage of Total" to the Chart after both "Row Total and "Column Total"

[E12345]

Hi!
I have a simple chart with a set of rows, a column and totals (crosstab). What I need is in addition to showing column and row totals, to also display a "Percentage of Total" row total as well as column total.  
The chart is based on the dataset that contain three columns: Ticket #, Ticket Duration Band and Ticket Cost Band.
I am able to show the data as a crosstab chart, with "Ticket Cost Band" as Rows, "Ticket Duration band" as Columns, and Count of Tickets as Values, as shown on Picture 1. (pasted below).

Picture 1 - see cross tab on the right.

       

 

 What I need is the extra data in the red box - perventage of total on either side, as shown on Picture 2.

Picture 2.

Below is the dataset I use to get the data into the grid. I wish I could attach the PBIX, but only "Super Users" have that capability. 
Feel free to copy and paste this dataset into Excel. THANK YOU!!!

Ticket #	Ticket Duration Band	Ticket Cost Band
345	1.) Under 1 year	1.) Under $100
455	2.) 1 year - 3 years	3.) $200 - $300
565	1.) Under 1 year	3.) $200 - $300
666	1.) Under 1 year	1.) Under $100
335	2.) 1 year - 3 years	2.) $100 - $200
654	3.) 3 years - 6 years	3.) $200 - $300
456	2.) 1 year - 3 years	1.) Under $100
987	3.) 3 years - 6 years	2.) $100 - $200
667	1.) Under 1 year	1.) Under $100
564	1.) Under 1 year	3.) $200 - $300
646	1.) Under 1 year	1.) Under $100
654	3.) 3 years - 6 years	2.) $100 - $200

[nprasanthk]

Create additional measure like Count(ticketes) and drag it to chart then On top of Measue do like below.

[E12345]

It can be done, but I can only do that for one level summary (I am getting either percentages or totals - one or the other). But I need both - totals and percentages side by side. I think this might be achievable via tablix object on paginated reports, but not ia Power BI table.

To be clear - with your soltion I can either get the totals (marked in yellow) or percentages (marked in green). But I need both.

[nprasanthk]

https://www.analytics-tuts.com/percentage-of-total-using-dax-in-powerbi/ try this

[anilkapkoti]

Hi @E12345 ,
Try This One

 IF (
    ISINSCOPE ( 'Table'[ Ticket Duration Band ] )
        && ISINSCOPE ( 'Table'[ Ticket Cost Band ] ),
    SUM ( 'Table'[ Ticket # ] ),
    FORMAT (
        SUM ( 'Table'[ Ticket # ] )
            / CALCULATE ( SUM ( 'Table'[ Ticket # ] ), ALL ( 'Table' ) ),
        "0.0%"
    )
)

[E12345]

I just took your idea of a chart with "Percenatges only", made two such charts, and underlatyed then under my original chart! ;D Then I took four blue shapes and sort of created a frame out of my three charts construction. 
It looks ugly, but can work as a tool tip if I format it better, lol.

The three arrows are pointing to three separate charts where the oriiginal chart with values only is at the center, while the percentage ones are framing the center chart from the bottom and from the right side where I only show their totals ;P. 

[E12345]

Thank you very much for your solution, I tried to use it but unfortunately it is not enough for my purposes. Even though it shows percentages as totals, I need to display both the totals and the percentages, as per my description. We already have this report built in crystal reports, and the users require the same "look". So I was hoping to do the same reprot in Power BI. 

[E12345]

After doing some research I found that the way to display both totals and percentage of totals in groups is via "tablix" object that is a part of paginated report. Currently there is no way to do it using any visual in Power BI Report. However, paginated report visual can be imbedded into Power BI and the tablix chart can be used from within report builder to create the display I need. I hope that in the future Power BI reports can do the same...

[amitchandak]

@E12345 , You can replace total with % of total using isinscope

How to Switch Subtotal and Grand Total in Power BI | Power BI Tutorials| isinscope: https://youtu.be/smhIPw3OkKA

But if you want an additional row, you need to modify dimensions or use an additional dim

Example with custom totals

Power BI How to get the P&L formatting right: https://youtu.be/C9K8uVfthUU
Power BI How to get two columns format Profit and Loss Statement(P&L) right: https://youtu.be/WLg85yiMgHI
https://medium.com/microsoft-power-bi/power-bi-formatted-p-l-with-custom-sub-totals-and-blank-rows-e...

[E12345]

Hi Amichandak,

I will look at your solution in a bit (swamped at work), But I was hoping to use some sort of dax function that can work with virtual tables and make it happen. I need the format that I described, and as a last resort I was going to just use a whole bunch of small cards (KPIs) in place of every cell, and get the data that way (but it is so much work!).
Also, we are working off a live connection, so I cannot do a lot of things that Power BI offers, such as create physical tables and columns (I have to ask dba who maintains the database to do that). 
Now sure what you mean by "But if you want an additional row, you need to modify dimensions or use an additional dim". I suppose it has something to do with new physical column creation, which I cannot do (it takes a long while to get those from the back end admins as well). So, I usually opt to rely on measures that I can make, and on Power BI Community ;).