Forum Discussion
Wrong selectedvalue calculation
Hi!
I have a report with a slicer by Code, when I select any of this code I need to see the summarize of this material with this code and the material with code ¨-¨.
I'm trying to use SELECTEDVALUE but it only shows me the total of "-" for all materials.
I don´t know what I am missing..
Total = SUMX(FILTER(ALL(Sheet),Sheet[Code]=SELECTEDVALUE(Sheet[Code],Sheet[Code]="-")),Sheet[amount1])
what I really want to see:
Material 1234 Material 4567
Code 1 Code 2
amount1 = 66 amount1 = 81
amount2 = 36 amount2= 8
what I have:
Material 1234 Material 4567
Code 1 Code 2
amount1 = 36 amount1 = 79
amount2 = 25 amount2 = 1
Thank you!
Sheet
| material | code | amount1 | amount2 |
| 1234 | 1 | 23 | 5 |
| 1234 | 1 | 7 | 6 |
| 1234 | 2 | 34 | 32 |
| 1234 | - | 0 | 1 |
| 1234 | - | 32 | 1 |
| 1234 | - | 4 | 23 |
| 1234 | - | 0 | 0 |
| 4567 | 2 | 2 | 8 |
| 4567 | 1 | 45 | 0 |
| 4567 | - | 78 | 1 |
| 4567 | - | 1 | 0 |
| 4567 | - | 0 | 0 |
| 8973 | - | 0 | 9 |
| 8973 | 1 | 32 | 9 |
| 8973 | - | 5 | 9 |
| 8973 | 2 | 4 | 5 |
| 8973 | 1 | 0 | 2 |
| 8973 | - | 1 | 1 |
| 8973 | - | 1 | 3 |
Anonymous,
It seems that your expected result is not correct based on the requirement "when I select any of this code I need to see the summarize of this material with this code and the material with code ¨-¨". I create the following columns firstly.specificcodeforamount1 = CALCULATE(SUM(Table1[amount1]),FILTER(Table1,Table1[material]=EARLIER(Table1[material])&&Table1[code]="-"))
specificcodeforamount2 = CALCULATE(SUM(Table1[amount2]),FILTER(Table1,Table1[material]=EARLIER(Table1[material])&&Table1[code]="-"))
And then create the measures below. For more details, please review attached PBIX file.Measure = SUM(Table1[amount1])+MAX(Table1[specificcodeforamount1])
Measure 2 = SUM(Table1[amount2])+MAX(Table1[specificcodeforamount2])
Regards,
Lydia
1 Reply
Anonymous,
It seems that your expected result is not correct based on the requirement "when I select any of this code I need to see the summarize of this material with this code and the material with code ¨-¨". I create the following columns firstly.specificcodeforamount1 = CALCULATE(SUM(Table1[amount1]),FILTER(Table1,Table1[material]=EARLIER(Table1[material])&&Table1[code]="-"))
specificcodeforamount2 = CALCULATE(SUM(Table1[amount2]),FILTER(Table1,Table1[material]=EARLIER(Table1[material])&&Table1[code]="-"))
And then create the measures below. For more details, please review attached PBIX file.Measure = SUM(Table1[amount1])+MAX(Table1[specificcodeforamount1])
Measure 2 = SUM(Table1[amount2])+MAX(Table1[specificcodeforamount2])
Regards,
Lydia