Using values from a Visual filter in a measure

[Jensej]

Hello! 

So i have a yearly Target Amount stored in a table. 15 780.-

Now i have a visual where my target value is 15 780.-. In my visual im able to filter the months if i for example want to look at  first quarter i check 1,2,3,4 from month. 

The problem is that my Target stays at 15 780.- but i want it to also change when i choose less months. 

Therefor i want to make a Measure where i say the Target is 15 780.- from start with all 12 months but if only 6 is selected the Target has to be 7890.-. 

Is there some way to get the selected month/months into a measure? 

This i what it looks like when i choose 1st quarter:

[darentengmfs]

@Jensej 

Are you trying to split the values evenly by month?

[Jensej]

Yeah i dont have different budget (target) for different months. It's the yearly budget / 12

[darentengmfs]

@Jensej 

If that's the case, I would use Power Query to insert months into your data.

Go to Power Query > Add Custom Column > Enter the formula: {1..12}

When the column is created, click the double arrow and choose Expand to New Rows

After that, you can either add a new column using Power Query or in DAX for your columns, e.g. Monthly Apple = [Apple]/12

This will give you the amount for each month.

You now have your month numbers that you can use to filter. If you wish to convert your month number into month name, please refer to the link below.

https://stackoverflow.com/questions/42964605/how-to-get-month-name-from-month-number-in-power-bi

If you wish to convert month number into Quarter number, use the following formula:

Quarter = "Q" & ROUNDUP('Table'[Month]/3,0)

[Jensej]

Hi @darentengmfs 

Thanks for answers.

So i did what you said.  Here is my how my Table look like atm. 

I also connected the month and year to my date table

Why does my Sum of AHT Grün not show only the Sum of 1st quarter? It's showes the whole year.

[darentengmfs]

@Jensej 

That's because the relationship isn't done right. If you could see the dotted lines in your relationship view, that means the relationship is not active. You cannot have 2 different relationships for the same 2 tables.

What I will do next is concatenante your month number to your year with a specific date in your data table. I would do this in Power Query by adding a new column.

Rest of the World -

Date:  "1/" & [Month] & "/" & "[Year]

OR

North America Version -

Date: [Month] & "/1/" & [Year]

Use the formula depending on your date formats. Apply the query, and use the newly created column in for your relationship between data table and date table.

[Jensej]

Tried this one 

Rest of the World -

Date:  "1/" & [Month] & "/" & "[Year]

It shows an error right away so i took the " before the Year away so i could save. Like this

Rest of the World -

Date:  "1/" & [Month] & "/" & [Year]

Now i get an error saying. Expression.Error: We cannot apply operator & to types text and number...

[darentengmfs · Accepted solution]

@Jensej 

I apologize for the misplaced " in the formula.

Before you create the date column, click on Month, change the data type to text. Same thing goes to year. Then, you create the date column, after that, change the data type of the date column to date.

[darentengmfs]

@Jensej 

Is the relationship between the months table and the amount table defined?

[Jensej]

Hi @darentengmfs 

No it's just a table with single budget amounts without any other information ( No dates ) 

Like this: 

Apples | Pineapples | Pear

 300     |       700      |   450

[darentengmfs]

@Jensej 

If your data does not have date values or anything that is related to dates, Power BI will not know what dates those are in.

[Jensej]

@darentengmfs  I understand but isnt possible at all to see what i choose in the Visual Filter and use these inputs in a measure? 

Measure  = 

Variable = Target/12

CALCULATE( SUM(SELECTEDMONTHS)*Variable)

Something like this