Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Mapping percentages on line graph with multiple variables...

Hi Everyone

 

I hope you're all well.

 

I'm normally quite good at finding solutions for topics by searching Google and watching YouTube videos but this one has got me stumped.  Probably a really simple solution and I've just been over complicating it in my head!  I'm trying to create a line graph in Power BI to track year on year pecentage values using various variables.  So each year adds up to 100%, but for whatever reason Power BI will only polt a graph using a percentage of the grand total.  I can create what I want in a matrix table but I can get the same soluton in a graph.

 

Below is a simpler and similar version of the data table I'm working on and also a table showing what I'm after and then the graph thats not playing ball.  Once I get this wortking I want to lay over some more complex variables but this is what I need to start.  I hope one of you has a solution.

 

Thank you in advance

 

Pauley

 

 

 

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous , Based on what I got you need YOY, you can use a date/year table

     

    example

    YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
    Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
    This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
    Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

     

    //Only year vs Year, not a level below

    This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
    Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

     

    diff = [This Year]-[Last Year ]
    diff % = divide([This Year]-[Last Year ],[Last Year ])

     

    plot that using Year from Date/year table

     

    Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
    https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    I created a table for testing.

    Then create a measure.

    Measure = 
VAR _value_1 = SUM('Table'[Qty Sold])
VAR _value_2 = CALCULATE(SUM('Table'[Qty Sold]),ALLEXCEPT('Table','Table'[Year]))
RETURN
DIVIDE(_value_1,_value_2)

    Matrix and Fields.

    Line Chart and Fields.

    Attach the PBIX file for reference. Hope it helps.

     

    Best Regards,
    Community Support Team_Gao

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly -- How to provide sample data

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi CST

       

      Thank you that works great.  I now need to add another level of complexitly.  Is it possible to build a similar line graph which will show only 1 colour and a line for 'Brand 1', a line for total of all brands and then a thrid line for the 'Good' brands?

       

      I basicaly want to show a visual line graph to show how close year by year percentage wise 'Brand 1' is against 'Good' brands (Brand 1 and 2 together), against the total volume.