Forum Discussion
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 | $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?
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.
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.
14 Replies
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.
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"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.
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.
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.
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.
I do not have the PBI file. Share the file with my formula already applied there and show the problem/expected result clearly.
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.
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.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.
