Grouped percentage of row total

[tempranello]

Hi there

It looks like I may have bumped up against a Power BI limitation when compared to Excel.  Perhaps it's coming in a later release?  Perhaps I'm just daft and I'm missed it in Power BI today:

I need to produce a chart that illustrates, by month, the percentage of tickets resolved by each team.

Imagine a table where each row represents a ticket, with attributes:  Ticket ID, Resolved month, Resolution team.

In Excel:  If I create a pivot chart (line chart) from this table, I can plot x-axis as Resolved month, the legend as Resolution team, and the values as Ticket ID.  This is now the cool part:  I can alter the Value Field settings (Ticket ID) to not just be Count, but I can show the value as '% of Row Total'.  Hurrah!  I get a brilliant line chart which shows for any given month what percentage of tickets are closed out by my Service Desk, Infrastructure Team etc etc.

Now, Power BI is great.  While I have the same query as the table above, the visualisation are limited.  Hmmm.  I could manually create a query for each team's resolution numbers against a grouping of Resolved month, and then merge all of the queries together and add columns to reflect the % each team's resolution volume represented, but geez, that's clunky and assumes that I know that numbers of teams that're in play.

Have a I missed something?

Thanks for your help!

[Greg_Deckler]

Create a measure something like:

% of Row Total = COUNT([TicketID])/CALCULATE(COUNT[TicketID],ALL(Table))

[tempranello]

Thanks for that.

I see where you're going with that.  I've used

% of Row Total = COUNTA('All tickets'[Ticket ID])/CALCULATE(COUNTA([Ticket ID]),ALL('All tickets'))

However, this doesn't work as I need the totals to be divided by totals per month rather than the entire total.  It's the grouping that's got me stumped.

All tickets table contains Ticket ID, Resolved by team, Resolved month ending.

If I produce a table in Power BI Desktop with a measure of COUNTA([Ticket ID]) I get soooo close to what I want:

I added a filter to show only 4 of the resolving teams, as expected I get 4 rows per month ending date.  What I now need to do is divide each row's 'Count of Ticket ID' by the sum of those values for each Resolved month ending. 

Eg.  Month ending 29 February 2016:  It has a total of 1,980 resolved tickets (188+52+1608+132).  I need to turn those individual ticket counts and divide them by that month's total to produce 9.49% for Messaging & Management, 2.63% for Networking, 81.21% for Service Desk and 6.67% for Unifed Communications.

While it'll be great when the GUI matures to Excel standards, I appreciate your guidance so I can learn how to DAX my way out of this.

Cheers!

[Sean · Accepted solution]

@tempranello this should work...

[Rubal]

Hi Sean,

please help with my formula. first column is the item description and second is quantity sold. I am trying to work out the % of total for each item description.

[Anonymous]

Hi Sean,

I applied your formula into my measure and it does not work?

Basically the concept is the same as I would like to derive the percentage value based on subcategory not grand total.

SumOfTotalCountOfMachineStatusByOwner = DIVIDE(

                                          COUNT('Machines-20190219'[Machine_status2]),

                                          Calculate(Count('Machines-20190219'[Machine_status2]),
                                               Allexcept('Machines-20190219','Machines-20190219'[Owner]))

)

It should be something like the following...

Please help thanks.

[Anonymous]

Hi Sean,

I applied your formula into my measure and it does not work?

Basically the concept is the same as I would like to derive the percentage value based on subcategory not grand total.

SumOfTotalCountOfMachineStatusByOwner = DIVIDE(

                                          COUNT('Machines-20190219'[Machine_status2]),

                                          Calculate(Count('Machines-20190219'[Machine_status2]),
                                               Allexcept('Machines-20190219','Machines-20190219'[Owner]))

)

It should be something like the following...

Please help thanks.

[Superjoie]

Hi, 

I tried to do your formula but I'm got an incorrect output. May you kindly check what I did wrong? The columns with % should total 100% each. Thanks much. 

[annerogers]

I could not get this to work for me.

I did see in the table view now I can see there is a % by row.  But if I switch to line and clustered column chart there is only % by grand total available as an option again.  Any idea when that will be fixed using the graphics?

Thank you.

[jorgeaguirre]

.

[Vvelarde]

@jorgeaguirre

Try replacing the All Except instead of Count put Tenure.

Regards

Victor

[lbrazi01]

Hi!

Im new in Power BI and I cant find, how to make table like this:

CATEGORY	BRAND	W1	W2	W3	W4	W5	W6	W7	W8	W9	W10	W11	…	TOTAL
PRIVATUS SEKTORIUS	BITĖ	20%	5%	0%	0%	21%	31%	26%	18%	0%	31%	33%	29%	22%
	OMNITEL	40%	48%	55%	45%	5%	0%	0%	0%	0%	0%	0%	0%	8%
	TELE2	20%	10%	45%	55%	73%	69%	54%	53%	62%	51%	36%	49%	48%
	TELIA	20%	37%	0%	0%	0%	0%	20%	29%	38%	19%	31%	22%	21%
TOTAL		100%	100%	100%	100%	100%	100%	100%	100%	100%	100%	100%	100%	100%
PREPAID SEKTORIUS	EŽYS	0%	0%	51%	30%	100%	0%	0%	30%	46%	34%	7%	0%	38%
	LABAS	0%	0%	0%	20%	0%	0%	58%	42%	49%	30%	35%	100%	19%
	PILDYK	100%	100%	49%	50%	0%	100%	42%	28%	6%	36%	58%	0%	43%
TOTAL		100%	100%	100%	100%	100%	100%	100%	100%	100%	100%	100%	100%	100%

I find only how to calculate total column percentage (with measure formula:

Measure1 = DIVIDE (SUM(tabale[Ind. 4+]),CALCULATE(SUM(tabale[Ind. 4+]),ALLEXCEPT(table,table[kategorija])),  0)

), but if I add weeks in columns, then measure formula work incorect... could anybody help me with this issue?

[anakoom]

Hi 

would you be help me with my case: as it is not calculating the correct one:

startdate , subdate, amount,%oftotal

1/1/2016   2/1/2016  100      50%

                  2/2/2016  100      50%

-----------------------------------------

                                    200     100%

i wants to see similar like above 

appriciate if you can help me

thanks

[Saaharjit]

What if i have a date slicer. It does not take into account.

[Greg_Deckler]

Use ALLEXCEPT instead of ALL?

[anakoom]

when i use this in matrix it wont be calculate by sub date it only refers to main date 

let me know if this would be possible in powerBI

[anakoom]

not working :

Measure = DIVIDE(sum(Sheet1[amount]),CALCULATE(SUM(Sheet1[amount]),ALLEXCEPT(Sheet1,Sheet1[startdate].[Date])),0.00)

is the formula i am using

[Saaharjit]

Try this

Measure = DIVIDE(sum(Sheet1[amount]),CALCULATE(SUM(Sheet1[amount]),ALLEXCEPT(Sheet1,Sheet1[startdate])))

[anakoom]

Any help would be appreciated

[anakoom]

when i am using the matrix it wont be working.

let me know how i can do it in matrix

[Hozcar]

Maybe this could help!

http://www.daxpatterns.com/cumulative-total/