Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

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.

 

powerbiuser444_0-1625165801342.png

Please see the attached pbix

 

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

1 ACCEPTED SOLUTION
Payeras_BI
Super User
Super User

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" } )
              )
    )

 

Payeras_BI_0-1625565931703.png

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

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

View solution in original post

5 REPLIES 5
Payeras_BI
Super User
Super User

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" } )
              )
    )

 

Payeras_BI_0-1625565931703.png

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

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

@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
Super User
Super User

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

Payeras_BI_0-1625319526873.png

 

B) 

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

Payeras_BI_1-1625319916074.png

 

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

@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
Super User
Super User

Hi @powerbiuser444 ,

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

Payeras_BI_0-1625218103017.png

Payeras_BI_1-1625218111993.png

Payeras_BI_2-1625218128809.png

 

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors