Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Making a Total Year bar chart

Hello everyone thank you for reading,

 

My client asked me to make a chart as seen below, in excel its pretty simple, but in Power Bi not so much.

 

I have 3 tables, Budget, Spending, Date table.

 

Is there a way to get this chart?

 

And furthermore, can you add a date filter so you can know how much you've spent until lets say Jun-21?

 

 

 

Thank you for your answers,

Best of luck,

 

Need Help
Tutorial Requests
  • parry2k's avatar
    parry2k
    5 years ago

    Anonymous I did this solution a long time ago and never had the opportunity to write a blog post on this. You can tweak the solution as your need, it has everything you need.

     

    Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

     

     

  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi  Anonymous ,

     

    First duplicate date column in your fact table and set the new column as text.

    Then create a dimension table as below:

    Slicer table = UNION(VALUES('Table'[Date]),ROW("Date","Total Year"))

    Finally create a measure as below:

    Measure =
IF (
    MAX ( 'Slicer table'[Date] ) IN FILTERS ( 'Table'[_date] ),
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Category] = MAX ( 'Table'[Category] )
                && 'Table'[_date] = MAX ( 'Slicer table'[Date] )
        )
    ),
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Category] = MAX ( 'Table'[Category] ) )
    )
)

    And you will see:

    For the related .pbix file,pls see attached.

     

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

7 Replies

  • parry2k's avatar
    parry2k
    Super User
    5 years ago

    Anonymous of course you cannot use it as a date. sorry, I'm out of giving the ideas. Good luck. Not sure what else I can do.  Ask yourself you are merging two types of data, date and a text "Total". Just think logically.

  • parry2k's avatar
    parry2k
    Super User
    5 years ago

    Anonymous I did this solution a long time ago and never had the opportunity to write a blog post on this. You can tweak the solution as your need, it has everything you need.

     

    Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi,

      Thank you for your kind help,

       

      There is something I'm trying to understand, every expression starts with: IF( MAX(Period[Period])="TOTAL" 

      For example,

       

       

      IF( MAX(Period[Period] ) = "TOTAL", CALCULATE( SUM( Data[Amount] ), ALLSELECTED( Data ) ) )

       

       

       

       

      The Period table is the following:

      Period Sort
      66
      88
      1010
      1212
      Total13

       

       

      Are the 2 Totals referring to the same? 

      Or does the Total in the period table not affect the MAX function?, is it necesary to have that total in the table?

       

      Thank you again,

       

       

       

       

  • parry2k's avatar
    parry2k
    Super User
    5 years ago

    Anonymous yes you need to have the Total row in the table otherwise we will not able to plot it, and value 13 is given to sort the Period column so that Total is shown at the end.

     

    There is a reason why everything is done that way. You have to follow what is done in this file, implement it in yours and it should work.

     

    Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi thank you for your quick reply, it was really insightful.

      Unfortunately I can't have a "Total" in my column since Power Bi can´t format it as Date.

       

      Best of luck!

      • v-kelly-msft's avatar
        v-kelly-msft
        Community Support
        5 years ago

        Hi  Anonymous ,

         

        First duplicate date column in your fact table and set the new column as text.

        Then create a dimension table as below:

        Slicer table = UNION(VALUES('Table'[Date]),ROW("Date","Total Year"))

        Finally create a measure as below:

        Measure =
IF (
    MAX ( 'Slicer table'[Date] ) IN FILTERS ( 'Table'[_date] ),
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Category] = MAX ( 'Table'[Category] )
                && 'Table'[_date] = MAX ( 'Slicer table'[Date] )
        )
    ),
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Category] = MAX ( 'Table'[Category] ) )
    )
)

        And you will see:

        For the related .pbix file,pls see attached.

         

         

        Best Regards,
        Kelly

        Did I answer your question? Mark my post as a solution!