Customized data display in matrix not shown when data is empty

[hainguyen28]

I have a dashboard that shows sales in the previous 13 months of 3 categories. Each category has a different way to display the sales:

• Furniture: Integer number
• Office supplies: Percentage
• Technology: Decimal

Here is what I have for the DAX formula:

However, if one month has missing data and I select that month in the filter, the format display is reset. For example, in the above picture, August 2012 data is missing. If I put August 2012 in the filter, all the format is automatically switched to percentage as per the picture below:

What can I do to fix this?

My dashboard is here: https://drive.google.com/file/d/10zT1TsJRhfPBYNb_3DP_oOHxC2OPbikw/view?usp=sharing

[v-pnaroju-msft]

Hi hainguyen28,

We have not received a response from you regarding the query and were following up to check if you have found a resolution. If you have identified a solution, we kindly request you to share it with the community, as it may be helpful to others facing a similar issue.

If you find the response helpful, please mark it as the accepted solution and provide kudos, as this will help other members with similar queries.

Thank you.

[v-pnaroju-msft · Accepted solution]

Thank you, @lbendlin , for your response.

Hi hainguyen28,

We appreciate your inquiry on the Microsoft Fabric Community Forum.

Please find attached the screenshot and the code, which may assist in resolving the issue:

Total Sales Last 13 Months =

VAR SelectedMonth = MAX('Calendar'[Date])

VAR SalesAmount =

CALCULATE(

SUM(Orders[Sales]),

DATESINPERIOD(

'Calendar'[Date],

SelectedMonth,

-13,

MONTH

)

)

VAR TotalSales = [Total Sales]

VAR SalesPercentage = DIVIDE(SalesAmount, TotalSales, 0)

VAR ProductCategories = VALUES('Orders'[Product Category])

RETURN

IF(

ISBLANK(SalesAmount),

BLANK(),

CONCATENATEX(

ProductCategories,

SWITCH(

TRUE(),

'Orders'[Product Category] = "Furniture", FORMAT(SalesAmount, "#,##0"),

'Orders'[Product Category] = "Office Supplies", FORMAT(SalesPercentage, "0.00%"),

'Orders'[Product Category] = "Technology", FORMAT(SalesAmount, "0.00"),

FORMAT(SalesAmount, "0.00")

),

", "

)

)

If you find our response helpful, kindly mark it as the accepted solution and share your appreciation. This will support other community members facing similar queries.

Thank you.

[lbendlin]

You can streamline your code a bit.  See if that helps

[hainguyen28]

Thanks for sending this over. However, I don't think this really solved my problem. In my original graph, the user can choose the reporting month and data within the previous 12 months would show. This would be the problem if the month I choose has no data. All the formatting would disappear. Do you happen to have any other solutions that still retain my original matrix and slicer functionality?

[lbendlin]

I don't think that is a good UX. Maybe someone else can assist you further.