Forum Discussion
Help with Creating a Measure that Excludes Customers with No Sales from Two Other Measures
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:
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(
18 Replies
Hi,
Does this measure work?
Measure 3 = CALCULATE ( [Line Amount USD], FILTER(dimProduct, ProductCategory = "Product 3"&&[Measure 2]<>blank()&&[Measure 1]<>blank()))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.
Hi,
I will need to see the PBi file. Show the problem there clearly and also show the expected result.
