The Value does not show when expand Metrix Table

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]))))

Need Help

Anonymous

1 year ago

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]))

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.

6 Replies

MattiaFratello
Super User
1 year ago
Do you have a correct relationship between your Data Report'[Sales Unit] and the Dim table for products?
DataNinja777
Super User
1 year ago
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
  Helper I
  1 year ago
  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.
  - Anonymous
    Not applicable
    1 year ago
    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.

Forum Discussion

The Value does not show when expand Metrix Table

6 Replies

Related content

expanded table

Expanding table in Power Query - preview shows correct value, but after expanding its incorrect

Metrix

Expand value from table

Expand Error