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

[powerbiuser444]

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!

[Payeras_BI · Accepted solution]

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 

[powerbiuser444]

@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

[Payeras_BI]

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
)

[powerbiuser444]

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

[Payeras_BI]

Hi @powerbiuser444 ,

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