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.

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.