Getting original data after drillup

[srinivas_fabric]

I have a matrix visual with rows category, Subscription.Colums Custom and Column1.
In values Section I have field Q1 Per2%.I have hierarchy With Custom and Column1.
I have used this hierarchy in columns section.my requirement is in Q1 main column I have subcolumns Jan, Feb, Mar and Q1 before drillup.
I have applied drill up on matrix visual. After drill up I want only Q1 column.
I am getting Q1 Column after drillup. But not getting the values in Q1 column like before drillup. Please help me how to get valuesin Q1 Column After drillup.This is sample data.

Thanks and regards.

[DataNinja777 · Accepted solution]

Hi @srinivas_fabric ,

When drilling up in a Power BI matrix visual, it’s common to lose the aggregated values you saw at the more granular level—like Jan, Feb, Mar disappearing and leaving Q1 blank. This happens because the measure you’re using is probably evaluated at the visible level of the hierarchy, and once you drill up, the month-level context is gone. If your DAX measure was depending on that, it simply returns nothing. Hardcoding specific months like “Jan”, “Feb”, and “Mar” inside the measure might seem like a fix at first, but it’s not scalable, especially if you want to apply the same logic to Q2, Q3, or future years.

The best practice here is to rely on your Calendar table, which should already have columns for Date, Month, Quarter, and Year. Assuming your Calendar table is related to your fact table through the date field, you can write a measure like this:

Q_Per2% =
VAR SelectedQuarter = SELECTEDVALUE('Calendar'[Quarter])
RETURN
DIVIDE(
    CALCULATE(
        SUM('Data'[Value]),
        'Calendar'[Quarter] = SelectedQuarter
    ),
    CALCULATE(
        SUM('Data'[Target]),
        'Calendar'[Quarter] = SelectedQuarter
    )
)

This way, the calculation works at any level—whether you're drilled into months or rolled up to quarters—because it's always looking at the Calendar context dynamically. It doesn't rely on hardcoding, and it automatically adapts when the user drills up or down in the hierarchy. The key is ensuring the calendar table is properly related and has the granularity needed to support flexible, context-aware calculations like this.

Best regards,

[v-tejrama · Accepted solution]

Hi @srinivas_fabric, 

To resolve the issue where aggregated values disappeared after drilling up in a Power BI matrix visual, we set up proper time intelligence. We created a Calendar table using DAX, including columns like Date, MonthName, MonthNumber, Quarter, and Year.

This table was linked to the fact table through the Date column to maintain accurate time relationships. To ensure months display in the correct order, we sorted the MonthName column by MonthNumber.

Then, we created a dynamic DAX measure that calculates totals based on the selected Quarter and Year, avoiding hardcoded values like “Q1.”

This measure was added to the matrix visual, with Quarter and MonthName used in the row hierarchy, and drill functionality enabled. As a result, the matrix now shows month-level breakdowns correctly and displays accurate totals when drilling up to the quarter level.

If this helps, kindly click "Accept as Solution" and give a "Kudos" so others can benefit. Let me know if you need further assistance!

Thank you,
Tejaswi.

[v-sgandrathi]

Hi @srinivas_fabric,

we haven't heard back from you regarding our last response and wanted to check if your issue has been resolved.

If our response addressed by the community member for  your query, please mark it as Accept Answer and give us Kudos. Should you have any further questions, feel free to reach out.
Thank you for being a part of the Microsoft Fabric Community Forum!

Thank you.

[v-tejrama]

Hi @srinivas_fabric,

Since we haven't heard back from you yet, I'd like to confirm if you've successfully resolved this issue or if you need further help?
If you've already resolved the issue, you can mark the helpful reply as a "solution" so others know that the question has been answered and help other people in the community. Thank you again for your cooperation!
If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.

 
Thank you,
Tejaswi.

[v-tejrama]

Hi @srinivas_fabric,

May I ask if you have resolved this issue? If so, please mark the helpful reply and Accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you,
Tejaswi.

[DataNinja777 · Accepted solution]

Hi @srinivas_fabric ,

When drilling up in a Power BI matrix visual, it’s common to lose the aggregated values you saw at the more granular level—like Jan, Feb, Mar disappearing and leaving Q1 blank. This happens because the measure you’re using is probably evaluated at the visible level of the hierarchy, and once you drill up, the month-level context is gone. If your DAX measure was depending on that, it simply returns nothing. Hardcoding specific months like “Jan”, “Feb”, and “Mar” inside the measure might seem like a fix at first, but it’s not scalable, especially if you want to apply the same logic to Q2, Q3, or future years.

The best practice here is to rely on your Calendar table, which should already have columns for Date, Month, Quarter, and Year. Assuming your Calendar table is related to your fact table through the date field, you can write a measure like this:

Q_Per2% =
VAR SelectedQuarter = SELECTEDVALUE('Calendar'[Quarter])
RETURN
DIVIDE(
    CALCULATE(
        SUM('Data'[Value]),
        'Calendar'[Quarter] = SelectedQuarter
    ),
    CALCULATE(
        SUM('Data'[Target]),
        'Calendar'[Quarter] = SelectedQuarter
    )
)

This way, the calculation works at any level—whether you're drilled into months or rolled up to quarters—because it's always looking at the Calendar context dynamically. It doesn't rely on hardcoding, and it automatically adapts when the user drills up or down in the hierarchy. The key is ensuring the calendar table is properly related and has the granularity needed to support flexible, context-aware calculations like this.

Best regards,