Sum of a column based on a selected value

[LABORSAL]

I'm trying to figure out how to get a measure to sum a column based on a selected value

Here's what I've got so far:

1) Created a table with possible selectable values

Possible selectable values in filter

1.5) Used SELECTVALUES() to get a "variable" which is updated by the filter

2) I have a table with various columns (3 of them are "Cases", "NSV" and "Tons"), what I'd like to do is create a measure which changes which column is referenced based on the SELECTEDVALUE() from the previous point.

I can do something like:

TONS = SUMX('Data Table', 'Data Table'[Tons])

But if I try to nest the value to replace the "Tons" like this (or any other way I've thought of) it just causes an error: 

VarVal = SUMX('Data Table', 'Data Table'[Value Type Selected])

Has anyone managed to do something similar to this work?

[v-gizhi-msft]

Hi,

According to your description, i create a table to test:

Then, unpivot it in Query Editor:

After Apply&Close, create a measure:

Measure = CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Attribute]=SELECTEDVALUE('Table'[Attribute])))

When choose [Attribute], it shows the sum result of the attribute you have chosen:

Hope this helps.

Best Regards,

Giotto Zhi

[OwaisAkber]

I have tried your solution with my problem but its not working. Below is my data structure. Table name is age_data. (this is inventory aging report and data is taken on different intervels.)

age -> different age bins like 0-30, 31-60, 61-90, over 90 ->type is text

date -> date of record -> data type is date

qty -> quantity of items -> data type is whole number

item_id -> id linked with item master -> data type is text

i have written below measure but its not working.

Comparitive Qty =

calculate( sum( age_data[qty] ), filter( age_data, age_data[date] = selectedvalue(age_data[date])

) )

If i add a manual date as second argument in selectedvalue function, its giving result but not giving with selected value in date slicer.

[LABORSAL]

This was actually a way I managed to do it but it started running slow when unpivoted for some reason (may be too big), this however does the trick 🙂

[amitchandak · Accepted solution]

Check are you looking for this ?

https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/50...

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[LABORSAL]

Found out how to do this yesterday, key points are:

1) Create a measure for each of the columns which just adds the columns

2) Create a switch based on the selected value which gives you the measure (can't be a column)