Help with Creating a Measure that Excludes Customers with No Sales from Two Other Measures

[spin1888]

I have a matrix visual row grouped by Customer, Product Category and Sub Product Category and three measures as values. However, on Measure 3 I want to exclude customers that had no sales in Measure 1 OR Measure 2 as the business is not interested in them for this study. I can't seem to figure out how to exclude in Measure 3 those customers we are not interested in looking at. The outcome always ends up including those customers of no interest.

I have tried using:

Flag = IF([Measure 1] || [Measure 2],1,0)

But I lose the respected total summarization in scope of the sub category totals and Grand total unexpectedly.

How do I go about summarizing Measure 3 to exclude customers with no sales in Measure 1 OR Measure 2 like the screenshot below keeping in mind that I will be adding and expanding by Customer, Product Category and then Product Sub Category:

Problem

Expected Result:

Below are the measures used (*** rewrote the measures below to keep it simplistic):

- Star Schema Data Model containing a dimProduct, dimCustomer and factSales table

Measure 1 = CALCULATE(

    [LineAmountUSD],

    FILTER('Product','Product'[Category]="Clothing")

)

Measure 2 = CALCULATE(

    [LineAmountUSD],

    FILTER('Product','Product'[Category]="Components")

)

Measure 3 = CALCULATE(

    [LineAmountUSD],

    FILTER('Product','Product'[Category]="Bikes")

)

*** The condition for Measure 3 shall sum [Line Amount USD] and filter on "Product 3" AND must exclude Customers with no sales in Measure 1 OR Measure 2

[FreemanZ]

Hi @spin1888 

How many relevant tables do you have and how are they related?

It seems you have a dimProduct table, at least a sales record table and maybe a customer table.

[spin1888]

Sorry about that; it is a star schema data model that has a dimProduct, dimCustomer and factSales tables

[FreemanZ]

can you describe how they are related and provide some sample data of each table?

[spin1888]

Sample file available here:

https://1drv.ms/u/s!AiGc_nsXDNeNgqE32eTthgrHIl3aKg?e=Mjju8t

[spin1888]

Unfortunately I can't provide sample data due to the sensitive nature but have a AdventureWorks pbix file mimicing the same scenerio.

[Ashish_Mathur]

Hi,

Does this measure work?

Measure 3 = CALCULATE ( [Line Amount USD], FILTER(dimProduct, ProductCategory = "Product 3"&&[Measure 2]<>blank()&&[Measure 1]<>blank())) 

[spin1888]

Thanks Ashish that worked but returned incorrect results. For example Customer A that had sales in Measure 1 and Measure 2 displayed $300 worth of sales for Measure 3 but I know for sure they are suppose to have $4,000 in sales.

[Ashish_Mathur]

Hi,

I will need to see the PBi file.  Show the problem there clearly and also show the expected result.

[spin1888]

Sure; what is the best way to send a pbix file. I have created a pbix file using the AdventureWorks as source and mimic the same scenerio I am working on.

[Ashish_Mathur]

Upload the file to Google Drive/One Drive and share the download link.

[spin1888]

Here is the temp link to my OneDrive:
https://1drv.ms/u/s!AiGc_nsXDNeNgqE32eTthgrHIl3aKg?e=Mjju8t

[Ahmedx]

Measure 3 =
VAR __tbl = CROSSJOIN ( VALUES ( Customer[Customer] ), VALUES ( 'Product'[Category] ))
VAR _m3 =
CALCULATE (
[LineAmountUSD],
FILTER ( 'Product', 'Product'[Category] = "Bikes" )
)
RETURN
IF (
NOT ( ISBLANK ([Measure 1] ) ) || NOT ( ISBLANK ([Measure 2] ) ),
IF (
ISINSCOPE ( Customer[Customer] ),
_m3,
SUMX (__tbl,_m3)))

[spin1888]

Thank you Ashish;
We are getting close. The following below worked as needed keeping the grand total at $94 M:

Measure 3 =

IF(NOT(ISBLANK([Measure 1])) || NOT(ISBLANK([Measure 2])),

CALCULATE(

    [LineAmountUSD],

    FILTER('Product','Product'[Category]="Bikes")

)

)

 The measure using the INSCOPE function worked but threw the grand totals way off:

However, $94 M was not the expected result when I sum up Measure 3. I was expecting the following in the tab "Expected Result":

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[spin1888]

Hey Ashish;

I tried to replicate it however I am noticing my results are coming in blank for [Measure] and [Measure 4]. Also, noticed that [Measures 1] and [Measure 2] repeat itself for every product category:

New File:

Replicated Tab:

[Ashish_Mathur]

Recheck everything with my solution file.  If you are still unable to solve, then share the download link of the PBI file.

[spin1888]

Beautifully well written. Thank you!!

[Ashish_Mathur]

You are welcome.