filter on multiple slicer and display the value

[Anonymous]

Hi Experts,

I have the table as below :

I have the two slicers:

1. Country

2. Month

what I need is, If I select the country and Month then the measure should calculate the "Jan" column and filter the country column and Code column then values should appear for 6025 against the Item code.

please help me to acheive this.

Regards

Swamy

[v-xicai]

Hi @Anonymous ,

You may combine all month columns into one month column, enter into Query Editor, click on all the month columns at the same time, go to transfer->Unpivot Columns to get picture 2, you can rename the column Attribute to Month, click "Close & Apply" button.

Then you can put the month into Slicer visual, create measure to get sum total like DAX below.

6025= CALCULATE(SUM(OPEX[Value]),FILTER(ALLSELECTED(OPEX), OPEX[Month] =MAX(OPEX[Month])&&OPEX[Code] = "6025"))

Best Regards,

Amy

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

[Anonymous · Accepted solution]

@v-xicai 

thanks for the reply, but already i have found the solution.

My measure as below:

6025-Functional Finance Advisors =

SWITCH([Selected],"Jan",CALCULATE(MAX(OPEX[Jan]),FILTER(OPEX,OPEX[Code] = "6025")),"Feb",CALCULATE(MAX(OPEX[Feb]),FILTER(OPEX,OPEX[Code]= "6025")),"Mar",CALCULATE(MAX(OPEX[Mar]),FILTER(OPEX,OPEX[Code] = "6025")),"Apr",CALCULATE(MAX(OPEX[Apr]),FILTER(OPEX,OPEX[Code] = "6025")),"May",CALCULATE(MAX(OPEX[May]),FILTER(OPEX,OPEX[Code] = "6025")),"Jun",CALCULATE(MAX(OPEX[Jun]),FILTER(OPEX,OPEX[Code] = "6025")),0)

Thanks,

Swamy

[Mariusz]

Hi @Anonymous 

You can Unpivot your data in Query Editor.

Go to >  Edit Queries > Select Your Table > Select All the Month Columns >  Click Unpivot Columns as below

This will create two columns Attribute for Months and Value for your Values

Now you can rename Attribute column to Month and use it in a slicer

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

[Anonymous]

@Mariusz 

the Month column is a calculated column.

whenever I select month from slicer then the value should filtered and change based on the slection .

[Mariusz]

@Anonymous 

I was referring to Month Columns ( Jan, Feb ....) 

Hope this helps.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

[Anonymous]

@Mariusz 

it's not working.

I have a measure as below:

6025 = var a = VALUES('Month'[Month]) return CALCULATE(SUM(OPEX[Jan]),FILTER(OPEX,OPEX[Code] = "6025"))

what I need is I want to calculate the Feb month as well for this code "6025"   in the same measure.

Any idea?