cancel
Showing results for
Did you mean:
Helper I

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

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!

1 ACCEPTED SOLUTION
Solution Sage
``````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" } )
)
)``````

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain
5 REPLIES 5
Solution Sage
``````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" } )
)
)``````

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain
Helper I

@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

Solution Sage

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

B)

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

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain
Helper I

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

Solution Sage

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

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain