Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Multi X Axis with different Columns/Measures.

I am looking for a Power BI visual just like the below snapshot.     The data looks like the below table   Date Country Supplier  Part Sub Part Total Qty Total Sales 02-06-2025...
Need Help
Show and Tell
Tips and Tricks
Nasif_Azam's avatar
Nasif_Azam
Super User
1 year ago

Hey Anonymous ,

To achieve a Power BI visual like the one you sketched with multi-bar (column) visuals showing QTD, YTD, Current Month, and Current Year comparisons based on Average Price.

Objective

Create two visuals (for Supplier A and B) showing Average Price = Total Sales / Total Qty

With these metrics on the X-axis:

  • QTD (Quarter-To-Date)

  • YTD (Year-To-Date)

  • M (Current Month)

  • Y (Current Year Total)

Step 1: Build Measures

In your Power BI data model, create measures using DAX:

AvgPrice_QTD :=
VAR QTD_Sales = CALCULATE(SUM('Table'[Total Sales]), DATESQTD('Table'[Date]))
VAR QTD_Qty = CALCULATE(SUM('Table'[Total Qty]), DATESQTD('Table'[Date]))
RETURN DIVIDE(QTD_Sales, QTD_Qty)

AvgPrice_YTD :=
VAR YTD_Sales = CALCULATE(SUM('Table'[Total Sales]), DATESYTD('Table'[Date]))
VAR YTD_Qty = CALCULATE(SUM('Table'[Total Qty]), DATESYTD('Table'[Date]))
RETURN DIVIDE(YTD_Sales, YTD_Qty)

AvgPrice_Month :=
VAR M_Sales = CALCULATE(SUM('Table'[Total Sales]), DATESMTD('Table'[Date]))
VAR M_Qty = CALCULATE(SUM('Table'[Total Qty]), DATESMTD('Table'[Date]))
RETURN DIVIDE(M_Sales, M_Qty)

AvgPrice_Year :=
VAR YearStart = DATE(YEAR(TODAY()), 1, 1)
VAR Y_Sales = CALCULATE(SUM('Table'[Total Sales]), 'Table'[Date] >= YearStart)
VAR Y_Qty = CALCULATE(SUM('Table'[Total Qty]), 'Table'[Date] >= YearStart)
RETURN DIVIDE(Y_Sales, Y_Qty)

Step 2: Create an Unpivoted Supporting Table for Visual

Create a new table:

AvgPriceView =
DATATABLE(
    "Period", STRING,
    "MeasureName", STRING,
    {
        {"QTD", "AvgPrice_QTD"},
        {"YTD", "AvgPrice_YTD"},
        {"M", "AvgPrice_Month"},
        {"Y", "AvgPrice_Year"}
    }
)

Then, create a switch measure:

AvgPrice_Switch :=
SWITCH(
    SELECTEDVALUE(AvgPriceView[MeasureName]),
    "AvgPrice_QTD", [AvgPrice_QTD],
    "AvgPrice_YTD", [AvgPrice_YTD],
    "AvgPrice_Month", [AvgPrice_Month],
    "AvgPrice_Year", [AvgPrice_Year]
)

Step 3: Create the Visual

  1. Use a Column Chart.

  2. Place AvgPriceView[Period] on the X-axis.

  3. Use the AvgPrice_Switch as Values (Y-axis).

  4. Add a Slicer for Supplier to allow dynamic filtering between Supplier A and B.

For Detailed Information:

Microsoft Docs – Time Intelligence Functions

Radacad – How to Use a Disconnected Table for Dynamic Axis

SQLBI – Using SWITCH for Multiple Measures

Power BI Community – Custom X Axis in Column Chart

 

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.


Best Regards,
Nasif Azam

Anonymous's avatar
Anonymous
Not applicable
1 year ago

This seems to be good,

But if in the Slicer 2 Suppliers are selected, how do I show one visual for Supplier A and another visual for Supplier B? That was also a part of the use case. Can you help me with it?

  • Nasif_Azam's avatar
    Nasif_Azam
    Super User
    1 year ago

    I provide you the detailed solution with steps, which will be very easy to impliment for you. To handle multiple supplier selections (e.g., both A and B) and still show separate visuals for each, you'll need to do a little modeling and setup in your Power BI report.

    Solution

    You’ll create two visuals, each filtered to display data only for one supplier, regardless of what's selected in the slicer.

    Step 1: Add a Slicer for Supplier

    • Add a slicer visual and put the Supplier field in it.

    • Enable multi-select if needed (so users can pick A and B together).

    Step 2: Create Two Measures with Supplier Filters

    AvgPrice_QTD_SupplierA :=
VAR QTD_Sales = CALCULATE(SUM('Table'[Total Sales]), DATESQTD('Table'[Date]), 'Table'[Supplier] = "A")
VAR QTD_Qty = CALCULATE(SUM('Table'[Total Qty]), DATESQTD('Table'[Date]), 'Table'[Supplier] = "A")
RETURN DIVIDE(QTD_Sales, QTD_Qty)

    Repeat similarly for:

    • AvgPrice_YTD_SupplierA
    • AvgPrice_Month_SupplierA
    • AvgPrice_Year_SupplierA

    And do the same for Supplier B with 'Table'[Supplier] = "B".

    Step 3: Create a Period Selector Table (Disconnected)

    PeriodTable =
DATATABLE(
    "Period", STRING,
    {
        {"QTD"},
        {"YTD"},
        {"Month"},
        {"Year"}
    }
)

    Create a slicer using this table, or use it as your X-axis in visuals.

    Step 4: Create SWITCH Measures for Each Supplier

    Supplier A:

    AvgPrice_Switch_SupplierA :=
SWITCH(
    SELECTEDVALUE(PeriodTable[Period]),
    "QTD", [AvgPrice_QTD_SupplierA],
    "YTD", [AvgPrice_YTD_SupplierA],
    "Month", [AvgPrice_Month_SupplierA],
    "Year", [AvgPrice_Year_SupplierA]
)

    Supplier B:

    Do the same with [AvgPrice_QTD_SupplierB], etc.

    Step 5: Build the Visuals

    • Create two clustered column charts:

      • One for Supplier A using PeriodTable[Period] on X-axis and AvgPrice_Switch_SupplierA as values.

      • One for Supplier B using the corresponding measure.

    • Hide axis titles and manually label the visuals above/below as “Supplier A” and “Supplier B” for clarity.

     

    For Detailed Information:

    Microsoft Docs – Time Intelligence Functions

    Radacad – How to Use a Disconnected Table for Dynamic Axis

    SQLBI – Using SWITCH for Multiple Measures

    Power BI Community – Custom X Axis in Column Chart

     

    If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.


    Best Regards,
    Nasif Azam