Forum Discussion

hainguyen28's avatar
hainguyen28
Regular Visitor
1 year ago
Solved

Customized data display in matrix not shown when data is empty

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 Techno...
Need Help
  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    1 year ago

    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.




v-pnaroju-msft's avatar
v-pnaroju-msft
Community Support
1 year ago

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.