Matrix Visual & Product Line Attributes

[liz_rowden]

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	$500	Y	JAN

I would like to create a Matrix Visual that has the Year/Month in the Rows and the Product Line in the column headers.  Values will be Sale Amount.  The part I am having a problem with is that there are also attributes on the Sales Order that would make the sales show in two Product Lines (but without inflating the Total Sales).  The sale amounts will be in their respective columns based on the Product Line, but there will also need to be a column for the Industrial Sales so the the same sale amount will be in two columns

The end result should be like below:

	BAKERY	DELI	INDUSTRIAL	TOTAL SALES
JAN	$100	$500	$600	$700

I can certainly do this by creating a separate Measure for each Product Line, but I would prefer not to do that.  The reason I don't want to do this is because of the Drill-through capability and each time they add/change a Product Line I would have to modify the report. Is there a way to do this by creating a single Calculated Column or single Measure?

[Ashish_Mathur · Accepted solution]

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]

Hello @liz_rowden,

We hope you're doing well. Could you please confirm whether your issue has been resolved or if you're still facing challenges? Your update will be valuable to the community and may assist others with similar concerns.

Thank you.

[v-ssriganesh · Accepted solution]

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.

[v-ssriganesh]

Hello @liz_rowden,

Hope everything’s going great with you. Just checking in has the issue been resolved or are you still running into problems? Sharing an update can really help others facing the same thing.

Thank you.

[Ashish_Mathur · Accepted solution]

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.

[liz_rowden]

Would it be possible to share your .pbix?  I have tried the formula you suggest, but for some reason the Industrial sales where the Product Line on the item is not Industrial (i.e. Deli) are excluded.

[Ashish_Mathur]

I do not have the PBI file.  Share the file with my formula already applied there and show the problem/expected result clearly.

[v-ssriganesh]

Hi @liz_rowden,

Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to @johnt75 for sharing valuable insights.

Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.

Thank you for being part of the Microsoft Fabric Community.

[johnt75]

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]

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]

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]

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]

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]

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]

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.