topic Re: Sales measure to show products in a matrix based on slicer selection in DAX Commands and Tips

Sales measure to show products in a matrix based on slicer selection

powerbiuser444 — Fri, 02 Jul 2021 20:31:48 GMT

Hi all,

I have the following data: 4 campaigns with a set of products.

Campaign1 - Product1, Product2

Campaign2 - Product1, Product2

Campaign3 - Product3, Product4

Campaign4 - Product5, Product6

I built a data model and created a measure that shows corresponding products based on campaign selection in a slicer. It works fine for individual campaigns and sums up sales for campaigns with the same products (in my case Campaign1 and Campaign2). But I can't figure out how to get my measure to show all products for campaigns with different product types and sums them up.
For example, when I pick Campaign1 and Campaign3 in the slicer I want to see Total Sales, Product1, Product2, Product3, Product4 in the matrix. And same logic should be applied to other campaigns with different product types.

Please see the attached pbix

I feel like it should be an easy fix. Please help!

Re: Sales measure to show products in a matrix based on slicer selection

Payeras_BI — Fri, 02 Jul 2021 09:30:22 GMT

Hi powerbiuser444 ,

If I understood correctly this could be done with a simple SUM(Data[Value]) as a measure.

Re: Sales measure to show products in a matrix based on slicer selection

powerbiuser444 — Fri, 02 Jul 2021 20:38:56 GMT

Payeras_BI thanks for posting. I'm sorry I simplified my mock data, so It worked for this particular case because there was no overlapping in products with 0 sales. In real data, sales can be 0 for the products witch are associated with a campaign and obviously, 0 for the products with are not associated with this campaign. So the rows are not unique anymore and when I throw in your simple measure it will show all products for each campaign. I reuploaded my sample pbix so it's closer to real data. That's why I had to manually match products with campaigns in my measure. Could anyone help to fix my issue?

Re: Sales measure to show products in a matrix based on slicer selection

Payeras_BI — Sat, 03 Jul 2021 13:48:41 GMT

Hi powerbiuser444 ,
In this case would you consider any of the following as possible solutions?

A) Filtering the visual while still using SUM(Data[Value])?

Measure 3 = CALCULATE( SUM(Data[Value]), Data[Value]<>0 )

Re: Sales measure to show products in a matrix based on slicer selection

powerbiuser444 — Tue, 06 Jul 2021 04:13:42 GMT

Payeras_BI thanks for your options, but it's still not quite what I'm looking for. I still want to see a column with 0 sales if it is associated with the campaign and I want 'Total' to be the first column. If you check my measure I already got this, just can't figure out to get it working for multiple selections

Re: Sales measure to show products in a matrix based on slicer selection

Payeras_BI — Tue, 06 Jul 2021 10:16:47 GMT

Hi powerbiuser444,

Measure 3 = VAR SelectedAttribute = SELECTEDVALUE ( Attributes[Attribute] ) VAR SelectedCampaign = VALUES ( Campaigns[Campaign] ) RETURN IF ( SelectedAttribute = "Total Sales", CALCULATE ( SUM ( Data[Value] ), REMOVEFILTERS ( Attributes ) ), IF ( OR("Campaign1" IN SelectedCampaign,"Campaign2" IN SelectedCampaign), CALCULATE ( SUM ( Data[Value] ), Data[Attribute] IN { "Product1", "Product2" } ) ) + IF ( "Campaign3" IN SelectedCampaign, CALCULATE ( SUM ( Data[Value] ), Data[Attribute] IN { "Product3", "Product4" } ) ) + IF ( "Campaign4" IN SelectedCampaign, CALCULATE ( SUM ( Data[Value] ), Data[Attribute] IN { "Product5", "Product6" } ) ) )

Vote for this Idea > Table / Matrix: Total Column on first position