Use selectedvalue in a measure

[Anonymous]

Hello,

I am new to PowerBI. I have a dataset, sample of it: 

Version	Month	Value
LE05	1	10
LE05	2	15
LE05	3	18
LE05	4	20
LE05	5	40
LE04	1	50
LE04	2	20
LE04	3	10
LE04	4	30
LE04	5	25

I have a slicer with the monthnumber. When selecting the monthnumber, e.g. monthnumber 4, it should 'select' LE04 and do a sum of the values of ALL months. When selecting monthnumber 5, it should select LE05. etc.  I cannot find a solution. I tried to use a help table with columns month & Version but that is not working. Do you have any idea how to solve this?

[prateekraina]

Hi @Anonymous,

So do you mean whatever Month number you choose the corresponding Version number should be chosen and then it sum all the values of month which fall under that version?

If I am wrong then kindly share the expected output also in the similar way you have shared the sample data. That would help in providing solution quickly.

Prateek Raina

[Anonymous]

Hello Prateek,

You are right. So in my example when I choose monthnumber 4, in a matrix visual it should show the version LE04 with the sum of values ALL months: 50+20+10+30+25= 135.

[prateekraina · Accepted solution]

Hi @Anonymous,

There can be many ways to achieve your result. One of them is below:

1. In Query Editor, create a column called "Version Number" which I created using Column from Example option. Let this column store the number at the end of your Version.

2. Close and apply changes. Change the data type of this column from text to whole number in Power BI Desktop.

3. Create a calculated table which will store the DISTINCT month numbers. This table will be used to for Month Number slicer.

AllMonths = DISTINCT(
            Table1[Month]
            )

4. Create following measure which will hold SUM of the VALUE.

Sum Of Value = 
CALCULATE(SUM(Table1[Value]),
    FILTER(Table1,
        Table1[Version Number] = SELECTEDVALUE(AllMonths[Month])))

5. Now make sure the Month Number slicer is using the Month field from calculated table.
6. Use the Sum Of Value measure created in the visual where you intent to show the sum.

Thats it. You have achieved what you wanted.
Here is a snapshot:

Note: Use your table names in the expressions. Table1 must be renamed to your table which contains the actual data.

Prateek Raina

[Anonymous]

Hello Prateek,

First of all, thank you very much for your in depth help and the example you have built! What a great support! I tried your steps, and all seem to be good. However, I do not get the end result. Probably some basic / simple setting  I forgot. But I cannot find what. 

 

[Anonymous]

It is working now! I did put in a relation between the 2 tables but that was not a step you told me. I deleted the connection and now it works! Thank you very very much! 

[prateekraina]

Great !!