Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Monthly Sales

Hi

I need to show the sales for all 12 months as well as the total sales for all 12 months on the same axis on a bar chart. How would I add total sales for 12 months to the calculation?

  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi, 

    Please check the below picture and the attached pbix file.

    One of many ways to solve it is to create disconnected axis table.

     

    Axis Table New =
    UNION (
    SUMMARIZE ( 'Calendar', 'Calendar'[Month Name], 'Calendar'[Month] ),
    ROW ( "Month Name", "Total", "Month", 13 )
    )
     
    And then create a measure like below.
     
    Sales Total: =
    SWITCH (
    TRUE (),
    SELECTEDVALUE ( 'Axis Table New'[Month Name] ) = "Total", SUM ( Data[Sales] ),
    CALCULATE (
    SUM ( Data[Sales] ),
    TREATAS ( VALUES ( 'Axis Table New'[Month] ), 'Calendar'[Month] )
    )
    )
     
     
     

5 Replies

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

    Hi, 

    Please check the below picture and the attached pbix file.

    One of many ways to solve it is to create disconnected axis table.

     

    Axis Table New =
    UNION (
    SUMMARIZE ( 'Calendar', 'Calendar'[Month Name], 'Calendar'[Month] ),
    ROW ( "Month Name", "Total", "Month", 13 )
    )
     
    And then create a measure like below.
     
    Sales Total: =
    SWITCH (
    TRUE (),
    SELECTEDVALUE ( 'Axis Table New'[Month Name] ) = "Total", SUM ( Data[Sales] ),
    CALCULATE (
    SUM ( Data[Sales] ),
    TREATAS ( VALUES ( 'Axis Table New'[Month] ), 'Calendar'[Month] )
    )
    )
     
     
     
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi

      The solutions seems like it will work. Unfortunately the requirement changed and I now need to display the sales growth instead of sales for the individual months, total sales for 3 months once a selection is made, as well as 6 months and 12 months. How would I go about achieving this? 

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      So I would like my growth to look like the below with the individual months, 3 months, 6 months and 12 months after selecting November

       

  • v-robertq-msft's avatar
    v-robertq-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, 

    In my opinion, I still suggest you to mark Jihwan_Kimb’s reply as a solution because he has achieved the original requirement you posted in the case title.

    What’s more, the new requirement you posted can not be achieved in the Power BI in my opinion. Because you can not set the X-axis or data values manually neither in the column chart nor in the dataset tables. They are all based on the data source you connected to the Power BI desktop.

     

    Thank you very much!

     

    Best Regards,

    Community Support Team _Robert Qin