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 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?

  • johnt75's avatar
    johnt75
    Super User
    10 months ago

    You could add a new column [Is Really Industrial] which is true if either the current Industrial Sale flag is set or the product line item is Industrial. You could do this using DAX, Power Query or SQL if your data source is a DB.

    This would simplify the filter required, you wouldn't need an OR condition, you could just use the new column, and by including the new column in the drill through filters that should work as you want, I think.

    • liz_rowden's avatar
      liz_rowden
      Regular Visitor
      10 months ago

      I already have a column for [Is Really Industrial] on the Sales Order Lines using a calculated column in DAX.  How are you suggesting I use that column differently from the other suggestions?

       
      • johnt75's avatar
        johnt75
        Super User
        10 months ago

        You could amend the measure to be

        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] ), 'Sales'[Is Really Industrial] = "Y" ),
            CALCULATE (
                SUM ( 'Table'[Sale Amount] ),
                'Table'[Product Line on Item] = CurrentLine
            )
        ),
    SUM ( 'Table'[Sale Amount] )
)

        and on your drill-through target page add Sales[Is Really Industrial] to the drill through fields. I think that should allow you to drill through from the industrial row.