Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tempranello
Advocate I
Advocate I

Grouped percentage of row total

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!

1 ACCEPTED SOLUTION
Sean
Community Champion
Community Champion

25 REPLIES 25
Greg_Deckler
Super User
Super User

Create a measure something like:

 

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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:

 

Capture.PNG

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
Community Champion
Community Champion

@tempranello this should work...

% of Row Total - Tickets.png

 

% of row total.jpg

 

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
Not applicable

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]))

)

 

F2.PNG

 

It should be something like the following...

F1.png

Please help thanks.

 

Anonymous
Not applicable

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]))

)

 

F2.PNG

 

It should be something like the following...

F1.png

Please help thanks.

 

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. 

 

% of Funding.JPG

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

 

Try replacing the All Except instead of Count put Tenure.

 

Regards

 

Victor




Lima - Peru

Hi!

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

CATEGORYBRANDW1W2W3W4W5W6W7W8W9W10W11TOTAL
PRIVATUS SEKTORIUSBITĖ20%5%0%0%21%31%26%18%0%31%33%29%22%
 OMNITEL40%48%55%45%5%0%0%0%0%0%0%0%8%
 TELE220%10%45%55%73%69%54%53%62%51%36%49%48%
 TELIA20%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 SEKTORIUSEŽYS0%0%51%30%100%0%0%30%46%34%7%0%38%
 LABAS0%0%0%20%0%0%58%42%49%30%35%100%19%
 PILDYK100%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?

 

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

 

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

Use ALLEXCEPT instead of ALL?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

not working :

 

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

 

is the formula i am using

Try this

 

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

Any help would be appreciated

when i am using the matrix it wont be working.

let me know how i can do it in matrix

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.