Filter Issue

[Surekha_PM]

Hello,

Here is my data from one table to create a line graph;

Date	Category	Sub-Category	Value
1-May-24	Health & Fitness	Health & Fitness	215
1-May-24	Health & Fitness	Yoga Matt	409
1-May-24	Health & Fitness	Health & Fitness	115
1-May-24	Health & Fitness	Shoes	234
1-May-24	Beauty	Beauty	176
1-May-24	Beauty	Lipstick	104
1-May-24	Beauty	Lipbalm	354
1-May-24	Beauty	Beauty	153
1-May-24	Stationary	Stationary	318
1-May-24	Stationary	Stationary	197
1-May-24	Stationary	Stationary	318
1-May-24	Stationary	Notebook	104

In X-axis we have added the date and Y-Axis has the value Sub-Category wise.

Now we have a global filter of Sub-Category and category. What we are trying to do is when a user is selecting Category - Health & Fitness he should see the comparison of the value of category and sub-category. The graph should show total of health & fitness data in one line and Shoes and yoga matt in another line. And if a user is selecting shoes in sub-category the user should see the total of health & fitness compared with shoes only.

I used Removefilter option and created two measurement and added in the line graph which is working fine. But when the filter is set to "All" the data for both category is showing same which is wrong.

Any Suggestions for this?

Regards,
Surekha

[Anonymous]

Hi @Surekha_PM 

You can try the following measures.

Total_Cate = CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Sub-Category] in VALUES('Table'[Category])))

Total_sub = CALCULATE(SUM('Table'[Value]),FILTER('Table',NOT('Table'[Sub-Category] in VALUES('Table'[Category]))))

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Surekha_PM]

HI @Anonymous 

Currently, I am using this function, which is working fine but somehow it is not showing a proper graph. All the data for categories and sub-categories show the same number. 

Total Cate =  CALCULATE(sum('Table'[Value1])/SUM('Table'[Value2]),'Table'[Sub-Category] = 0,REMOVEFILTERS('Table'[Category]))

Total Sub =  CALCULATE(sum('Table'[Value1])/SUM('Table'[Value2]),'Table'[Sub-Category] = 1,REMOVEFILTERS('Table'[Sub-Category]))

Here we have created another table and created a function that says if Category = Sub-Category, then 0 or else 1. 

The graph is supposed to be like this:

But instead it is coming like this

Both the parameter is showing same number


Can you check now.

Regards,
Surekha