The Value does not show when expand Metrix Table

[cj_oat]

Hi all,

I have an issue regarding Matrix Table, so below is the screenshot where I expanded Matrix table but you may see that in the column "Month Cover" the number does not show in the sub-rows that I have expanded. Not sure if I need to change any setting or is it because of my Month Cover formula might be incorrect?

Month Cover = 
DIVIDE([Inventory],SWITCH(TRUE(),FORMAT(MAX('Data Report'[Today]),"DDD")="Mon",
CALCULATE(SUM('Data Report'[Sales Unit]),'Data Report'[Week Number]>=MAX('Data Report'[Latest Week])-3),
CALCULATE(SUM('Data Report'[Sales Unit]),'Data Report'[Week Number]>=MAX('Data Report'[Latest Week])-3 && 'Data Report'[Week Number]<MAX('Data Report'[Latest Week]))))

[DataNinja777]

Hi @cj_oat ,

It looks like the "Month Cover" values are missing in the sub-rows of your Matrix table. This issue could be due to either a formula limitation or a Matrix setting affecting how values are displayed at different hierarchy levels. The main problem might stem from the way MAX('Data Report'[Latest Week]) and MAX('Data Report'[Today]) behave when the Matrix expands. Since MAX() aggregates across multiple rows, it may not return the expected values at lower levels of granularity.

One possible solution is to replace MAX() with SELECTEDVALUE(), ensuring that the measure works at both summary and detail levels. Additionally, the measure should check whether 'Data Report'[Today] has a value in the current row context before applying calculations. A debug measure like this can help confirm if 'Data Report'[Today] is returning values at lower levels:

Debug = MAX('Data Report'[Today])

If this measure returns blank values for sub-rows, then the current row context does not have 'Data Report'[Today], which could explain why "Month Cover" isn't showing values when the Matrix expands.

To improve the DAX formula, the following approach ensures that the calculation is consistent across all levels:

Month Cover = 
VAR CurrentWeek = SELECTEDVALUE('Data Report'[Latest Week], MAX('Data Report'[Latest Week]))
VAR SalesCalc = 
    SWITCH(TRUE(),
        FORMAT(SELECTEDVALUE('Data Report'[Today]), "DDD") = "Mon",
        CALCULATE(SUM('Data Report'[Sales Unit]), 'Data Report'[Week Number] >= CurrentWeek - 3),
        CALCULATE(SUM('Data Report'[Sales Unit]), 'Data Report'[Week Number] >= CurrentWeek - 3 && 'Data Report'[Week Number] < CurrentWeek)
    )
RETURN
DIVIDE([Inventory], SalesCalc, BLANK())

This version of the formula introduces SELECTEDVALUE() to improve context awareness and ensure that CurrentWeek is derived correctly. The measure also avoids calculation errors by returning BLANK() instead of dividing by zero when no sales data is available. If values still do not appear, it may be helpful to check Matrix visual settings, such as enabling "Show values in rows" and ensuring that aggregation works correctly across the hierarchy. If the issue persists after applying this approach, let me know what results you observe.

Best regards,

[cj_oat]

Hi @DataNinja777 ,

I have tried changing DAX but it still does not work (still showing BLANK), however, if I removed sub-row to have just only one row (either "Consumer" row or "Product" row) then the Month Cover will show correctly. Dont know why when I put sub-row then the Month Cover number disappeared.

[v-jianpeng-msft]

Hi, @cj_oat 

I wish you all the best. Previously we have provided a solution to help you solve the problem. 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 yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.
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 for your patience and look forward to hearing from you.

Best Regards

Jianpeng Li

[v-jianpeng-msft]

Hi, @cj_oat 

When you delete either Consumer or Product, the Month Cover Measure has a value, which means:
When the external context satisfies both the Consumer and the Product, there is no corresponding data. So the result of the computation is empty, which is a very common external context problem.

To do this, apply a calculate function to your original metric and remove one of the row label filters via removefilters or all.

Month Cover = 
VAR CurrentWeek = SELECTEDVALUE('Data Report'[Latest Week], MAX('Data Report'[Latest Week]))
VAR SalesCalc = 
    SWITCH(TRUE(),
        FORMAT(SELECTEDVALUE('Data Report'[Today]), "DDD") = "Mon",
        CALCULATE(SUM('Data Report'[Sales Unit]), 'Data Report'[Week Number] >= CurrentWeek - 3),
        CALCULATE(SUM('Data Report'[Sales Unit]), 'Data Report'[Week Number] >= CurrentWeek - 3 && 'Data Report'[Week Number] < CurrentWeek)
    )
RETURN
CALCULATE(DIVIDE([Inventory], SalesCalc, BLANK()),REMOVEFILTERS('Data Report'[Consumer]))

OR

Month Cover = 
VAR CurrentWeek = SELECTEDVALUE('Data Report'[Latest Week], MAX('Data Report'[Latest Week]))
VAR SalesCalc = 
    SWITCH(TRUE(),
        FORMAT(SELECTEDVALUE('Data Report'[Today]), "DDD") = "Mon",
        CALCULATE(SUM('Data Report'[Sales Unit]), 'Data Report'[Week Number] >= CurrentWeek - 3),
        CALCULATE(SUM('Data Report'[Sales Unit]), 'Data Report'[Week Number] >= CurrentWeek - 3 && 'Data Report'[Week Number] < CurrentWeek)
    )
RETURN
CALCULATE(DIVIDE([Inventory], SalesCalc, BLANK()),ALL('Data Report'[Consumer]))

Best Regards

Jianpeng Li

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

[MattiaFratello]

Do you have a correct relationship between your Data Report'[Sales Unit] and the Dim table for products?