+ Reply to Thread
Results 1 to 21 of 21

PowerBI - how to show percentage calculation of aggregate AND segments on line chart

  1. #1
    Registered User
    Join Date
    04-19-2020
    Location
    London, England
    MS-Off Ver
    2007/2016/MS 365
    Posts
    55

    PowerBI - how to show percentage calculation of aggregate AND segments on line chart

    Hi, I'm a beginner to PowerBI and just learning by doing, looking for youtube videos etc. I do not know DAX or any coding at the outset.
    I'm trying to learn quickly for a deadline so please suggest simple solutions with minimal or simple coding please!

    On my sample spreadsheet I'm dealing with the yellow highlighted fields. There's s 'picture' of the result I want to get to. How can I:

    1) Show a line for 'All' (the Total) AND for each Gender. I can only seem to get just Male and Female alone

    2) I am filtering on 'Pass' just to show the 'Pass' but only getting the values. But what I want to show is the % passed out of the total cohort for each year. So for 'All' the Total % Passed out of all each year and for each Gender, the % Passed from each Gender total each year. And show everything Year by Year in a time series

    Do I need to do calculations in Excel first or can I/should I do it all in PowerBi and how?! Thanks
    Attached Files Attached Files

  2. #2
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    San Escobar but I could be anywhere in the world
    MS-Off Ver
    power BI
    Posts
    8,930

    Re: PowerBI - how to show percentage calculation of aggregate AND segments on line chart

    look here: https://learn.microsoft.com/en-us/power-bi/

  3. #3
    Registered User
    Join Date
    04-19-2020
    Location
    London, England
    MS-Off Ver
    2007/2016/MS 365
    Posts
    55

    Re: PowerBI - how to show percentage calculation of aggregate AND segments on line chart

    Thanks for replying - and for link, very useful. Over more time, will be learning. Any immediate helps though with specifics now greatly appreciated as I've been on youtube all day but not making headway!

  4. #4
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    San Escobar but I could be anywhere in the world
    MS-Off Ver
    power BI
    Posts
    8,930

    Re: PowerBI - how to show percentage calculation of aggregate AND segments on line chart

    did you try Power Query first or Power Pivot?
    XL365 contain it by default
    Last edited by sandy666; 09-22-2022 at 04:32 PM.

  5. #5
    Registered User
    Join Date
    04-19-2020
    Location
    London, England
    MS-Off Ver
    2007/2016/MS 365
    Posts
    55

    Re: PowerBI - how to show percentage calculation of aggregate AND segments on line chart

    Quote Originally Posted by sandy666 View Post
    did you try Power Query first or Power Pivot?
    XL365 contain it by default
    I have not. By executive order, it must be done by PowerBI. Not a user of those either (PowerQuery, PowerPivot)...steep learning curve!

    I took a quick look at the link you posted - where to start?! I took a quick look at 'create your own measures in PBI desktop' - which is already for advanced users who know the language etc. but it did say sigma signs are where the values can be aggregated. My data is mostly text apart from YEAR and person identifier numbers (in the live data). I'm sure it's possible to do but I just don't know how and quickly!

    I have managed to get to this (attached screenshot) but no further! And my Y axis is just counts not the %s required.
    Attached Images Attached Images
    Last edited by Questray; 09-22-2022 at 04:59 PM.

  6. #6
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    San Escobar but I could be anywhere in the world
    MS-Off Ver
    power BI
    Posts
    8,930

    Re: PowerBI - how to show percentage calculation of aggregate AND segments on line chart

    PowerBI is Power Query and Power Pivot together

    I'll re-read your post but not promising, maybe someone else
    I suggest PowerBI forum

  7. #7
    Registered User
    Join Date
    04-19-2020
    Location
    London, England
    MS-Off Ver
    2007/2016/MS 365
    Posts
    55

    Re: PowerBI - how to show percentage calculation of aggregate AND segments on line chart

    [QUOTE=sandy666;5730959]PowerBI is Power Query and Power Pivot together

    I see!


    No worries at all. Thanks a lot for pointers anyway. Where's a PowerBI forum, couldn't find very active looking ones like this one? The youtube vids are just not dealing with my issue/what I need as I can see anyway quickly.

    Would be good to just deal with the first one first - how to show the total together with Male and Female!
    Last edited by Questray; 09-22-2022 at 05:12 PM.

  8. #8
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    San Escobar but I could be anywhere in the world
    MS-Off Ver
    power BI
    Posts
    8,930

    Re: PowerBI - how to show percentage calculation of aggregate AND segments on line chart


  9. #9
    Registered User
    Join Date
    04-19-2020
    Location
    London, England
    MS-Off Ver
    2007/2016/MS 365
    Posts
    55

    Re: PowerBI - how to show percentage calculation of aggregate AND segments on line chart

    Great, let me have a look...

  10. #10
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    San Escobar but I could be anywhere in the world
    MS-Off Ver
    power BI
    Posts
    8,930

    Re: PowerBI - how to show percentage calculation of aggregate AND segments on line chart

    something like this?

    malefemale.png

    Year M F
    1950
    4
    4
    1951
    2
    4
    1952
    6
    4
    1953
    2
    2
    1954
    9
    5
    Last edited by sandy666; 09-22-2022 at 06:04 PM.

  11. #11
    Registered User
    Join Date
    04-19-2020
    Location
    London, England
    MS-Off Ver
    2007/2016/MS 365
    Posts
    55

    Re: PowerBI - how to show percentage calculation of aggregate AND segments on line chart

    Yes exactly, the Male, female and then a line showing combined total. In PBI though.
    Dragging the GENDER field (made up of the M and F) into the visualisation just gives the split into Male and Female. How to get the combined in addition as well.
    Last edited by Questray; 09-22-2022 at 05:58 PM.

  12. #12
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    San Escobar but I could be anywhere in the world
    MS-Off Ver
    power BI
    Posts
    8,930

    Re: PowerBI - how to show percentage calculation of aggregate AND segments on line chart

    try to adapt this code to Power Query in PBI
    this is the same language : M

    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    04-19-2020
    Location
    London, England
    MS-Off Ver
    2007/2016/MS 365
    Posts
    55

    Re: PowerBI - how to show percentage calculation of aggregate AND segments on line chart

    Hhhmm ok, let me take a look.

  14. #14
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    San Escobar but I could be anywhere in the world
    MS-Off Ver
    power BI
    Posts
    8,930

    Re: PowerBI - how to show percentage calculation of aggregate AND segments on line chart

    simpler

    Please Login or Register  to view this content.
    or easier

    Please Login or Register  to view this content.
    all commands are from menu not manually entered
    Last edited by sandy666; 09-22-2022 at 06:23 PM.

  15. #15
    Registered User
    Join Date
    04-19-2020
    Location
    London, England
    MS-Off Ver
    2007/2016/MS 365
    Posts
    55

    Re: PowerBI - how to show percentage calculation of aggregate AND segments on line chart

    ...Let me take a look, thank you.

  16. #16
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    San Escobar but I could be anywhere in the world
    MS-Off Ver
    power BI
    Posts
    8,930

    Re: PowerBI - how to show percentage calculation of aggregate AND segments on line chart

    You are welcome

  17. #17
    Registered User
    Join Date
    04-19-2020
    Location
    London, England
    MS-Off Ver
    2007/2016/MS 365
    Posts
    55

    Re: PowerBI - how to show percentage calculation of aggregate AND segments on line chart

    Will update on final result tomorrow! ..

  18. #18
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    San Escobar but I could be anywhere in the world
    MS-Off Ver
    power BI
    Posts
    8,930

    Re: PowerBI - how to show percentage calculation of aggregate AND segments on line chart

    Tomorrow is just today Good morning

  19. #19
    Registered User
    Join Date
    04-19-2020
    Location
    London, England
    MS-Off Ver
    2007/2016/MS 365
    Posts
    55

    Re: PowerBI - how to show percentage calculation of aggregate AND segments on line chart

    Haha yes

  20. #20
    Registered User
    Join Date
    04-19-2020
    Location
    London, England
    MS-Off Ver
    2007/2016/MS 365
    Posts
    55

    Re: PowerBI - how to show percentage calculation of aggregate AND segments on line chart

    Quote Originally Posted by sandy666 View Post
    @Sandy666: Thanks soooo much for all the above tips + site suggested = solved!

  21. #21
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    San Escobar but I could be anywhere in the world
    MS-Off Ver
    power BI
    Posts
    8,930

    Re: PowerBI - how to show percentage calculation of aggregate AND segments on line chart

    Glad to help

    if you are happy hit Add Reputation

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 10-23-2021, 08:50 PM
  2. [SOLVED] Line Trend Chart show Percentage vs term
    By sintek in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 03-10-2018, 11:54 AM
  3. putting a percentage calculation into a chart
    By LLOYD6664 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-18-2016, 08:18 PM
  4. [SOLVED] Chart Lengend to show marker style on hidden line - Line chart
    By etaf in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 08-22-2013, 05:19 PM
  5. Replies: 1
    Last Post: 03-23-2012, 10:17 PM
  6. Show line conditionally - Line Chart
    By idrisb59 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 04-20-2008, 12:45 PM
  7. [SOLVED] show aggregate total in pie chart
    By ecav in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 09-28-2005, 03:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1