Matrix with Measures in columns and Quaterly sub-columns

[vdiallonort]

Hello,

I am trying to do a matrix with Columns and sub-columns, but one way ( Quater- Measures ) does work out of the box but the other one (Measures -. Quater-Month doesn't naturally and the tricks I tried doesn't work.

So the final outcome i am looking for is the following :

So i tried to used a calculated table to be able to pivot my measures and a bridge table to link it to date to this list of measures :

The Pivot measures is just the list of measures :

PivotListMeasures = DATATABLE(

    "MeasureName", STRING,

    {

        {"Fixed Cost Converted"},

        {"NetSales Converted"},

        {"Variable Cost converted"}

    }

)

And the bridge table is a cross join between date and the list of measures :

BridgeDateMeasures = CROSSJOIN(Fiscal_date,PivotListMeasures)

Then i create a measure :

PivotMeasure =

SWITCH(

    SELECTEDVALUE(PivotListMeasures[MeasureName]),

    "NetSales Converted", CALCULATE([NetSales Converted]),

    "Fixed Cost converted", CALCULATE([Fixed Cost converted]),

    "Variable Cost converted", CALCULATE([Variable Cost converted]),

    BLANK()

)

Which i used in the value section of the matrix :
But now the quarter doesn't show up :

Any idea of why the part with the Quarter doesn't appear ? I missing a big bit of logic here so any kind explaination is welcome.

Cheers

Vincent

[SolomonovAnton · Accepted solution]

You're very close to your goal of building a matrix with Measures → Quarter → Month layout. Here's what's happening and how to fix it:

🔍Root Issue

The issue is related to the lack of a proper date hierarchy in your matrix columns. When you pivot measures using a table (like PivotListMeasures) and bridge it with dates, you're disrupting the natural date hierarchy used by Power BI (Year → Quarter → Month).

You’ve set MeasureName and Quarter as columns, but without including Month, the matrix can't split the quarters further.

---

✅Solution Strategy

1. Ensure Month is in the Bridge Table
   Update your BridgeDateMeasures to include a column for MonthName (e.g., "January", "February"), derived from Fiscal_date.

   BridgeDateMeasures =
   SELECTCOLUMNS(
       CROSSJOIN(Fiscal_date, PivotListMeasures),
       "fiscal_period_dt_yyyymmdd", Fiscal_date[fiscal_period_dt_yyyymmdd],
       "Quarter", Fiscal_date[Quarter],
       "YearFiscalPeriod", Fiscal_date[YearFiscalPeriod],
       "MonthName", FORMAT(Fiscal_date[fiscal_period_dt_yyyymmdd], "MMMM"),
       "MonthNum", MONTH(Fiscal_date[fiscal_period_dt_yyyymmdd]),
       "MeasureName", PivotListMeasures[MeasureName]
   )

2. Sort Month by MonthNum
   Set the MonthName column to be sorted by MonthNum to keep the correct month order.
3. Use Month in Matrix Columns
   Adjust your matrix to use:
   • Rows: YearFiscalPeriod
   • Columns: MeasureName → Quarter → MonthName
   • Values: PivotMeasure
4. Validate Relationships
   Ensure BridgeDateMeasures is correctly joined to both Fiscal_date and PivotListMeasures via 1-to-many relationships.

---

🧠 Why This Works

This setup mimics a "normalized" perspective of your measures and allows the use of multiple time granularity levels in columns. Power BI will now treat your date levels as a hierarchy-like structure.

---

📘Additional Tips

• Make sure Quarter is in text format (e.g., "Q1", "Q2") or sorted properly.
• Use DAX Studio  to check the performance if matrix lags.

✔️If my message helped solve your issue, please mark it as Resolved!

👍If it was helpful, consider giving it a Kudos!

[vdiallonort]

Also i found how to sort the MonthName by MonthNum :

[vdiallonort · Accepted solution]

Thanks a lot for the information,unfortunatly it's still now work.I may have misunderstand your message so let me rephrase what i have done to correct my code according to you guideline :

1)  Add month in the bridge table :

2) Sorth month name by monthNum ( this one i didn't understand ) : Where should i setup the sorting ?
3) Add in the Column Quarter and Month coming from the Bridget table ( and not the Fiscal Date dimension )
Row is YearFiscalDate from the Fiscal Date dimension ( I also tried using the one from the bridge table )

4) I check the relationship

 The relationship is set between the bridge and FiscalDate and PivotMeasures

4.1 Bridge- FiscalDate

4.2 BridgetDateMeasures - PivotListMeasures

Could it be something with my Measures that mess it up ?

PivotMeasure =

SWITCH(

    SELECTEDVALUE(PivotListMeasures[MeasureName]),

    "NetSales Converted", CALCULATE([NetSales Converted]),

    "Fixed Cost converted", CALCULATE([Fixed Cost converted]),

    "Variable Cost converted", CALCULATE([Variable Cost converted]),

    BLANK()

)

[SolomonovAnton · Accepted solution]

You're very close to your goal of building a matrix with Measures → Quarter → Month layout. Here's what's happening and how to fix it:

🔍Root Issue

The issue is related to the lack of a proper date hierarchy in your matrix columns. When you pivot measures using a table (like PivotListMeasures) and bridge it with dates, you're disrupting the natural date hierarchy used by Power BI (Year → Quarter → Month).

You’ve set MeasureName and Quarter as columns, but without including Month, the matrix can't split the quarters further.

---

✅Solution Strategy

1. Ensure Month is in the Bridge Table
   Update your BridgeDateMeasures to include a column for MonthName (e.g., "January", "February"), derived from Fiscal_date.

   BridgeDateMeasures =
   SELECTCOLUMNS(
       CROSSJOIN(Fiscal_date, PivotListMeasures),
       "fiscal_period_dt_yyyymmdd", Fiscal_date[fiscal_period_dt_yyyymmdd],
       "Quarter", Fiscal_date[Quarter],
       "YearFiscalPeriod", Fiscal_date[YearFiscalPeriod],
       "MonthName", FORMAT(Fiscal_date[fiscal_period_dt_yyyymmdd], "MMMM"),
       "MonthNum", MONTH(Fiscal_date[fiscal_period_dt_yyyymmdd]),
       "MeasureName", PivotListMeasures[MeasureName]
   )

2. Sort Month by MonthNum
   Set the MonthName column to be sorted by MonthNum to keep the correct month order.
3. Use Month in Matrix Columns
   Adjust your matrix to use:
   • Rows: YearFiscalPeriod
   • Columns: MeasureName → Quarter → MonthName
   • Values: PivotMeasure
4. Validate Relationships
   Ensure BridgeDateMeasures is correctly joined to both Fiscal_date and PivotListMeasures via 1-to-many relationships.

---

🧠 Why This Works

This setup mimics a "normalized" perspective of your measures and allows the use of multiple time granularity levels in columns. Power BI will now treat your date levels as a hierarchy-like structure.

---

📘Additional Tips

• Make sure Quarter is in text format (e.g., "Q1", "Q2") or sorted properly.
• Use DAX Studio  to check the performance if matrix lags.

✔️If my message helped solve your issue, please mark it as Resolved!

👍If it was helpful, consider giving it a Kudos!

[vdiallonort]

Thanks it was actually working, I just need to add the data, so i can have a time hierarchy and click on the expend level on the matrice to see all level.

Regards

Vincent Diallo-Nort