Sales excluding Riesling product

[gbarr12345]

Hey,

I'm trying to create a measure that calculates the total sales excluding the product Riesling.

However, when I put the measure into a card it's giving me the overall figure which still includes Riesling.

Any modifications to my code that will fix this? Code is below.

Thank you in advance. 

Sales excluding Riesling =

CALCULATE(

    COUNTROWS('Module Sales with Inventory'),

    FILTER(

        'Module Sales with Inventory',

        NOT RELATED('Dimension Item'[description]) = "Riesling"

    )

)

[Ashish_Mathur]

Hi,

Share some data to work with, explain the question and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[gbarr12345]

Hi Ashish,

Essentially I'm looking to show all the sales between a TIME period excluding a certain product in this case it's Product B from the sample data below that I want to exclude.

I have sample data below pasted. I would like to have an outcome similar to the screenshot attached also if possible.

Dimension_Period Table
FYPeriod
20240301
20240302
20240303
20240304
20240305
20240306
20240307
20240308
20240309
20240310
20240311

Dimension_Customer Table
Customer Name
1
2
3
4
5
6
7

Dimension_Item Table

Item

A

B

C

D

Sales Table
Sales
100
125
150
112
124
178
112
135
123
121

[Ashish_Mathur]

How can the sales table have just a single column?

[gbarr12345]

These are all of the columns in the sales table but the amount field is the only one that I use from it with the other tables.

[v-zhouwen-msft]

Hi @Ashish_Mathur , @amitchandak ,thanks for the quick replies, I'll add further.

Hi @gbarr12345 ,

Regarding your question, I think there is a problem with the relationships established between the tables. Can you talk about what fields are in the table? By which fields are the relationships established between tables? With the sample data you have given, I have created the following relationship .

[gbarr12345]

Thank you for the response.

Please see the Fields and Relationships in the link below to my google drive as it wouldn't allow me to attach screenshots as it said Server busy:

https://docs.google.com/document/d/1PrXKp15uCeqjA2D4vKfrx92c2Xa3iPc2/edit?usp=drive_link&ouid=116472...

Hopefully this helps even further.

Thank you so much!

[gbarr12345]

Also with your above measure, my result is still showing the product I want it to exclude:

[v-zhouwen-msft]

Hi @gbarr12345 ,

I checked your link and the picture is a bit blurry. I don't think there is a problem with the relationship between the tables. I'm also confused as to why the measure don't filter out the corresponding products, but I've tested it myself and it works fine. Is it possible to share your .pbix file without sensitive data?

[gbarr12345]

Please find the link to my PBIX file now. Hopefully you can help using this:

https://drive.google.com/file/d/1MwFy4oJuBzzrmwJ7MPCSJkuMkFMU-xxl/view?usp=drive_link

[v-zhouwen-msft]

Hi @gbarr12345 ,

Regarding your question, I tested it and found that there seems to be no problem. The sales table shown below has a total of 99 rows of data. Suppose we need to exclude the sales of 'Apple Laptop'(Item Code = 1).

97 = 99 - 2

Calculating gross sales should also be fine, please try again.

[amitchandak]

@gbarr12345 , Table should already be joined,

You can try like

Sales excluding Riesling =
CALCULATE(
COUNTROWS('Module Sales with Inventory'),
FILTER(
'Dimension Item',
'Dimension Item'[description] <> "Riesling"
))

[gbarr12345]

Hi Amit,

Thank you for the response.

I tried your code and it still seems to be appearing to show the sales but including the Riesling sales.

Is there any tweak to fix this?

[gbarr12345]