Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi @everyone,
I would like to calculate a measure to add the values of rows. For example I have a slicer [subject] containing {subject1,subject2,subject3 etc...} when I deactivate Subject 3, the value of Subject 3 is added to the value of Subject 1.
subject | value |
subject 1 | 1 |
subject 2 | 1 |
subject 3 | 1 |
subject 4 | 1 |
subject 5 | 1 |
subject 6 | 1 |
Solved! Go to Solution.
To achieve this, you can create a measure in Power BI that dynamically adjusts the values based on the selection made in the slicer. You can use the SWITCH function along with the ISFILTERED function to determine which subject(s) are selected in the slicer, and then calculate the sum accordingly. Here's how you can do it:
```DAX
Total Value =
VAR SelectedSubjects =
VALUES ( 'YourTable'[subject] )
RETURN
SWITCH (
TRUE (),
COUNTROWS ( SelectedSubjects ) = 0, SUM ( 'YourTable'[value] ), -- If no subject selected, show total value
COUNTROWS ( SelectedSubjects ) > 1, BLANK (), -- If more than one subject selected, show blank (optional)
SELECTEDVALUE ( 'YourTable'[subject] ), -- If only one subject selected, show the value of that subject
CALCULATE (
SUM ( 'YourTable'[value] ),
ALL ( 'YourTable'[subject] ),
'YourTable'[subject] = SELECTEDVALUE ( 'YourTable'[subject] )
)
)
```
Replace `'YourTable'` with the actual name of your table, and `'subject'` and `'value'` with the names of your subject and value columns, respectively.
Here's how the measure works:
- It first checks if any subject is selected. If no subject is selected, it shows the total value.
- If more than one subject is selected, it shows blank (you can remove this line if you want to display something else in this case).
- If only one subject is selected, it shows the value of that subject.
- If a single subject is selected, it calculates the sum of values for that subject only.
This measure should give you the desired result of dynamically adjusting the values based on the selection made in the slicer. Adjust it as needed based on your specific requirements and data model.
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!
To achieve this, you can create a measure in Power BI that dynamically adjusts the values based on the selection made in the slicer. You can use the SWITCH function along with the ISFILTERED function to determine which subject(s) are selected in the slicer, and then calculate the sum accordingly. Here's how you can do it:
```DAX
Total Value =
VAR SelectedSubjects =
VALUES ( 'YourTable'[subject] )
RETURN
SWITCH (
TRUE (),
COUNTROWS ( SelectedSubjects ) = 0, SUM ( 'YourTable'[value] ), -- If no subject selected, show total value
COUNTROWS ( SelectedSubjects ) > 1, BLANK (), -- If more than one subject selected, show blank (optional)
SELECTEDVALUE ( 'YourTable'[subject] ), -- If only one subject selected, show the value of that subject
CALCULATE (
SUM ( 'YourTable'[value] ),
ALL ( 'YourTable'[subject] ),
'YourTable'[subject] = SELECTEDVALUE ( 'YourTable'[subject] )
)
)
```
Replace `'YourTable'` with the actual name of your table, and `'subject'` and `'value'` with the names of your subject and value columns, respectively.
Here's how the measure works:
- It first checks if any subject is selected. If no subject is selected, it shows the total value.
- If more than one subject is selected, it shows blank (you can remove this line if you want to display something else in this case).
- If only one subject is selected, it shows the value of that subject.
- If a single subject is selected, it calculates the sum of values for that subject only.
This measure should give you the desired result of dynamically adjusting the values based on the selection made in the slicer. Adjust it as needed based on your specific requirements and data model.
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!
User | Count |
---|---|
91 | |
73 | |
68 | |
63 | |
55 |
User | Count |
---|---|
96 | |
89 | |
73 | |
61 | |
58 |