Forum Discussion

achopda's avatar
achopda
New Member
1 year ago
Solved

Power BI Chart for Multiple X axis variants

I have a use case where I need to create comparison between 4 time frames in a bar chart with the naming in it.   A rough figure would be as seen below     I need to have YTD, QTY, CM, Y(Y...
Need Help
Show and Tell
Tips and Tricks
  • speedramps's avatar
    speedramps
    1 year ago

    Try this ...

     

    Create a calendar table with a Day offset, Month offset, Year offset and Quarter offset column.

    This example is based on 14/06/2025 

    QTD ave = 
CALCULATE(
    [Ave price],
    ALL('Calendar'),
    'Calendar'[Quarter offset] = 0)

    You can learn about calendar tables and offsets here.

    If you spend time and build a good one, then you will use it again and again on all your reports.

    So it is really important to learn about Calendars and Offsets.

    https://www.youtube.com/watch?app=desktop&v=BtYn1hfdSAM&t=0s

    https://www.youtube.com/watch?v=XjVLaVLluYE

     

    Your months will then have these values (based on 14/06/2025)

     

    Build a 1:M relationship from the calendar table to your fact table

     

    Create measures (not calculated columns)

    Ave price = 
DIVIDE( SUM(Sales[Value]) , SUM(Sales[Qty]) )

     

    MTD ave = 
CALCULATE(
    [Ave price],
    ALL('Calendar'),
    'Calendar'[Month offset] = 0)

     

    QTD ave = 
CALCULATE(
    [Ave price],
    ALL('Calendar'),
    'Calendar'[Quarter offset] = 0)

     

    YTD ave = 
CALCULATE(
    [Ave price],
    ALL('Calendar'),
    'Calendar'[Year offset] = 0)

     

    The CALCULATE and ALL command remove the natural dates filter and then apply the desired offset filter.

     

    Now draw a Clustered column chart

    Please click thumbs up because I have tried to help.

     

    Then click [accept solution] if it works.  Thank you ! ðŸ˜€ðŸ˜€ðŸ˜€

     

  • hnam_2006's avatar
    hnam_2006
    1 year ago

     

    PBIX Sales Comparison

    Hi,

    I’ve linked a dummy data file and a PBIX file that demonstrate my approach. The explanation below is based on these files.

    To create a Power BI bar chart that compares four different time frames—YTD (Year-to-Date), QTD (Quarter-to-Date), CM (Current Month), and Yearly Average Pricing—on a single X-axis with distinct category labels, here's how you can approach it:

    1. Prepare your data model:
      • Include a date table (see Building a date table for best practices).
      • Include a sales table (I used dummy data based on your structure).
      • Ensure a many-to-one active relationship between the date columns of both tables.
    2. Create separate measures for each time frame:
      YTD Pricing = CALCULATE(AVERAGE(Sales[VAL_SLS]), DATESYTD('Date'[Date]))
      QTD Pricing = CALCULATE(AVERAGE(Sales[VAL_SLS]), DATESQTD('Date'[Date]))
      CM Pricing = CALCULATE(AVERAGE(Sales[SLS_VOL]), DATESBETWEEN('Date'[Date], STARTOFMONTH('Date'[Date]), ENDOFMONTH('Date'[Date])))
      Year Avg Pricing = CALCULATE(AVERAGE(Sales[SLS_VOL]), FILTER(ALL('Date'), YEAR('Date'[Date]) = YEAR(MAX('Date'[Date]))))
    3. Create a supporting table for categories:
      TimeFrameTable = DATATABLE(
  "TimeFrame", STRING,
  {
    {"YTD"},
    {"QTD"},
    {"CM"},
    {"Year Avg"}
  }
)
    4. Create a clustered column chart:
      • X-axis: TimeFrame column
      • Y-axis: Pricing by TimeFrame measure
      • Small multiples: Supplier column

    Compare Sales Chart

    Best regards,
    hnam_2006

    If this post helps, please consider accepting it as a solution to help others find it more quickly.

hnam_2006's avatar
hnam_2006
Frequent Visitor
1 year ago

PBIX Sales comparison 

 

Hi achopda 

 

I have linked a dummy data file and pbix file which will show you my approach. below explaination is based on the same

 

To create a Power BI bar chart that compares four different time frames—YTD (Year-to-Date), QTD (Quarter-to-Date), CM (Current Month), and Yearly Average Pricing—on a single X-axis with distinct category labels, here's how you can approach it:

 

1) Prepare your data model:

you need a dataset which includes

--> date table (check and follow the Building a date table for best practice)

--> Sales table (i have taken dummy data using columns from your table and created solution)

--> Have a relationship between date column of each table (many to one (active)

 

2) Create a separate measure for all Timeframes: 

YTD Pricing = CALCULATE(
    AVERAGE(
        Sales[VAL_SLS]),
        DATESYTD('Date'[Date])
)
QTD Pricing = CALCULATE(
    AVERAGE(
        Sales[VAL_SLS]),
        DATESQTD('Date'[Date])
)
CM Pricing = CALCULATE(
    AVERAGE(Sales[SLS_VOL]),
    DATESBETWEEN('Date'[Date],
    STARTOFMONTH('Date'[Date]),
    ENDOFMONTH('Date'[Date])
    )
)
Year Avg Pricing = 
CALCULATE(
    AVERAGE('Sales'[SLS_VOL]),
    FILTER(
        ALL('Date'),
        YEAR('Date'[Date]) = YEAR(MAX('Date'[Date]))
    )
)

 

3) Create a supporting table for categories:

use DAX to create a new table under "Table View"

TimeFrameTable = DATATABLE(
    "TimeFrame", STRING,
    {
        {"YTD"},
        {"QTD"},
        {"CM"},
        {"Year Avg"}
    }
)

 

4) Create a unified measure to help in creating the chart

Pricing by TimeFrame = 
SWITCH(
    SELECTEDVALUE(TimeFrameTable[TimeFrame]),
    "YTD", [YTD Pricing],
    "QTD", [QTD Pricing],
    "CM", [CM Pricing],
    "Year Avg", [Year Avg Pricing]
)

 

5) Create a clustered column chart:

--> X-axis - TimeFrame column

--> Y-axis - Pricing by TimeFrame measure

--> Small multiples - Supplier column

 

 

best regards

hnam_2006

If this post helps, then please consider Accept it as solution to help the other members find it more quickly