Power BI Chart for Multiple X axis variants

[achopda]

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(Year) average Pricing. But I need to understand how can I get 4 different category labels in one x-axis. The Data would look like below where Average Price is AMT/VOL column

Also, this should be comparing it between two visuals. More or less like If I select 2 supplier one visual shows me for Supplier A and another shows be for Supplier B. Please can someone help me design the same in Power BI?

Date	COUNTRY	Supplier	Parent	Child	Cat	VAL_SLS_USD_AMT	SLS_VOL
01-05-2019	X	A	B	25	A	1000	50
01-05-2019	X	A	B	26	A	2000	60
01-05-2019	X	A	B	27	A	3000	70

[v-hashadapu]

Hi @achopda , I wanted to check with you and see if the information provided by @hnam_2006 was useful. If any of it helped resolve your question, consider marking it as "Accept as Solution" to make it easier for others to find. Let me know if there's anything else I can assist with!

[hnam_2006]

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

Best regards,
hnam_2006

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

[hnam_2006]

[speedramps]

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 ! 😀😀😀

[DataNinja777]

Hi @achopda ,

To achieve your Power BI chart showing average pricing across MTD, QTD, CM, and YTD for selected suppliers, you'll need to build separate DAX measures for each timeframe and use a disconnected table to create the category labels on the x-axis. Start by defining a base measure for average price:

Avg Price = DIVIDE(SUM('YourTable'[VAL_SLS_USD_AMT]), SUM('YourTable'[SLS_VOL]))

Then, define measures for each time frame using CALCULATE and appropriate time intelligence functions:

AvgPrice_MTD = CALCULATE([Avg Price], DATESMTD('Date'[Date]))

AvgPrice_QTD = CALCULATE([Avg Price], DATESQTD('Date'[Date]))

AvgPrice_YTD = CALCULATE([Avg Price], DATESYTD('Date'[Date]))

AvgPrice_CM = CALCULATE(
    [Avg Price],
    FILTER(ALL('Date'), 
        MONTH('Date'[Date]) = MONTH(TODAY()) &&
        YEAR('Date'[Date]) = YEAR(TODAY()))
)

Create a disconnected table to represent the x-axis categories:

TimeFrame = DATATABLE(
    "Period", STRING,
    {
        {"MTD"},
        {"QTD"},
        {"CM"},
        {"YTD"}
    }
)

Then create a dynamic measure that reacts to the selected Period value from the disconnected table:

AvgPrice_ByPeriod = 
SWITCH(
    SELECTEDVALUE(TimeFrame[Period]),
    "MTD", [AvgPrice_MTD],
    "QTD", [AvgPrice_QTD],
    "CM", [AvgPrice_CM],
    "YTD", [AvgPrice_YTD]
)

In your visual, use TimeFrame[Period] on the x-axis and AvgPrice_ByPeriod as the value. You can duplicate the visual and set one to filter for Supplier A and the other for Supplier B to create a side-by-side comparison, or use the Supplier as a legend in a clustered column chart to show both in one visual. This setup lets you create the custom category x-axis and control the calculation behind each bar without hardcoding or reshaping the model unnaturally.

Best regards,

[hnam_2006]

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