Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello, I would like my matrix columns to be split based on if Employee is selected ("Yes") or not selected ("No").
I have my Base Table
Product | Employee | Units |
Apple | A | 1 |
Pear | A | 2 |
Apple | B | 3 |
Pear | B | 4 |
Apple | C | 5 |
Pear | C | 6 |
and Selection Table
Employee |
A |
B |
C |
Here is an example of the desired outcome if I select "A" from the selection table
Product | In Selection | Out of Selection |
Apple | 1 | 8 |
Pear | 2 | 10 |
I am new to Power BI and DAX, my experience in mostly in Tableau. If there is a solution that doesn't require the Selection Table, that is good too.
Thanks!
Solved! Go to Solution.
Hi! You're going to want to create two different measures that use the SELECTEDVALUE function, one for the total for employee selected and the other for all employees except the selected one.
Probably not syntactically perfect, but this should be close...
VAR
SelectedEmployee = SELECTEDVALUE(Employee[Employee])
RETURN
CALCULATE(
SUM(BaseTable[Units])
Employee[Employee] = SelectedEmployee
)
VAR
SelectedEmployee = SELECTEDVALUE(Employee[Employee])
RETURN
CALCULATE(
SUM(BaseTable[Units])
Employee[Employee] <> SelectedEmployee
)
Hi! You're going to want to create two different measures that use the SELECTEDVALUE function, one for the total for employee selected and the other for all employees except the selected one.
Probably not syntactically perfect, but this should be close...
VAR
SelectedEmployee = SELECTEDVALUE(Employee[Employee])
RETURN
CALCULATE(
SUM(BaseTable[Units])
Employee[Employee] = SelectedEmployee
)
VAR
SelectedEmployee = SELECTEDVALUE(Employee[Employee])
RETURN
CALCULATE(
SUM(BaseTable[Units])
Employee[Employee] <> SelectedEmployee
)
Thank you. This solution works. I don't like that Power BI doesn't have a dynamic "set/group" solution because that would only be one variable, rather than one for each measure. But since it looks like they don't have this option, this is a good solution. Thanks!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |