Forum Discussion

liz_rowden's avatar
liz_rowden
Regular Visitor
10 months ago
Solved

Matrix Visual & Product Line Attributes

I have sales data that looks something like below: Item # Product Line on Item Sale Amount Industrial Sale? Month A100 BAKERY $100 N JAN A200 INDUSTRIAL $100 Y JAN A300 DELI...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    10 months ago

    Hi,

    These measures work

    S = SUM(Data[Sale Amount])
    Measure = if(HASONEVALUE(Data[Product Line on Item]),if(MAX(Data[Product Line on Item])="Industrial",CALCULATE([S],Data[Industrial Sale?]="Y",all(Data[Product Line on Item])),[S]),[S])

    Hope this helps.

  • v-ssriganesh's avatar
    v-ssriganesh
    10 months ago

    Hi liz_rowden,
    I reproduced the scenario, and it worked on my end. I used my sample data and successfully implemented it. I am also including .pbix file for your better understanding, please have a look into it:

    Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

    Thank you for using the Microsoft Fabric Community Forum.


    Best regards,
    Ganesh Singamshetty.

johnt75's avatar
johnt75
Super User
10 months ago

You could create a disconnected table to use in the matrix, which is the distinct values of the product lines, e.g.

Product Lines = DISTINCT( 'Table'[Product Line on Item] )

Use this table in your matrix visual, and create a measure like

Total Sales =
IF (
    ISINSCOPE ( 'Product Lines'[Product Line on Item] ),
    VAR CurrentLine =
        SELECTEDVALUE ( 'Product Lines'[Product Line on Item] )
    RETURN
        IF (
            CurrentLine = "INDUSTRIAL",
            CALCULATE (
                SUM ( 'Table'[Sale Amount] ),
                'Table'[Product Line on Item] = CurrentLine
                    || 'Table'[Industrial Sale?] = "Y"
            ),
            CALCULATE (
                SUM ( 'Table'[Sale Amount] ),
                'Table'[Product Line on Item] = CurrentLine
            )
        ),
    SUM ( 'Table'[Sale Amount] )
)

which should calculate the correct amount at the individual line and at the total level.

  • liz_rowden's avatar
    liz_rowden
    Regular Visitor
    10 months ago

    Thank you very much. I have tried that, but in the portion below. I get the following message:

    "The expression contains columns from multiple tables, but only columns from a single table can be used in a True/False expression that is used as a table filter expression."

     

    The Item # is in the Sales Order table and the Product Line attribute is in the Items table. Sorry I should have specified that earlier.  I tried adding the Product line to my Sales Order table, but then get the following message:

    "A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

     

     

                    SUM ( 'Table'[Sale Amount] ),
                'Table'[Product Line on Item] = CurrentLine
                    || 'Table'[Industrial Sale?] = "Y"

     

    • johnt75's avatar
      johnt75
      Super User
      10 months ago

      You could using FILTER, e.g.

      Total Sales =
IF (
    ISINSCOPE ( 'Product Lines'[Product Line on Item] ),
    VAR CurrentLine =
        SELECTEDVALUE ( 'Product Lines'[Product Line on Item] )
    RETURN
        IF (
            CurrentLine = "INDUSTRIAL",
            CALCULATE (
                SUM ( 'Table'[Sale Amount] ),
                FILTER (
                    Sales,
                    'Items'[Product Line on Item] = CurrentLine
                        || 'Sales'[Industrial Sale?] = "Y"
                )
            ),
            CALCULATE (
                SUM ( 'Table'[Sale Amount] ),
                'Table'[Product Line on Item] = CurrentLine
            )
        ),
    SUM ( 'Table'[Sale Amount] )
)

      Your Items table will belong to the expanded table of sales, so you should be able to place a filter on both columns.

      • liz_rowden's avatar
        liz_rowden
        Regular Visitor
        10 months ago

        Many thanks. This change did work to give me the correct totals in each of the Product Lines. However, I'm unable to get the drill-through to a sub-report to work. Is there a way to get that to work?