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.
I have created a whatif parameter and added it as a slicer on a page.
I have this table
ID | Amount |
1 | 12 |
1 | 40 |
1 | 10 |
1 | 20 |
2 | 30 |
2 | 40 |
2 | 50 |
3 | 60 |
3 | 70 |
I want to sum these values by ID and create a stacked bar chart which is divided into 4 sections based on the value of the silcer paramter,
- below 50%
- between 50 to 80%
- between 80 to 100%
- over 100%
I want to be able to see how many ID's are in each of these 4 categories in a stacked bar chart.
For example if the silcer value is set to 50
- below 50% - should include ID's with sum of Amount less 25
- over 100% - should include ID's with sum of Amount over 50
If the silcer value is set to 100
- below 50% - should include ID's with sum of Amount less 50
- over 100% - should include ID's with sum of Amount over 100
How would I go about this? Would I uses a switch case?
Solved! Go to Solution.
Hi @Anonymous ,
According to your description, here's my solution.
1.Create a section table containing the four sections.
2.Create a measure.
Measure =
VAR _P =
SELECTEDVALUE ( Parameter[Parameter] )
RETURN
SWITCH (
MAX ( 'Section Table'[Section] ),
"below 50%",
CALCULATE (
DISTINCTCOUNT ( 'Table'[ID] ),
FILTER (
ALL ( 'Table' ),
SUMX (
FILTER ( ALL ( 'Table' ), 'Table'[ID] = EARLIER ( 'Table'[ID] ) ),
'Table'[Amount]
) < 0.5 * _P
)
),
"between 50 to 80%",
CALCULATE (
DISTINCTCOUNT ( 'Table'[ID] ),
FILTER (
ALL ( 'Table' ),
SUMX (
FILTER ( ALL ( 'Table' ), 'Table'[ID] = EARLIER ( 'Table'[ID] ) ),
'Table'[Amount]
) >= 0.5 * _P
&& SUMX (
FILTER ( ALL ( 'Table' ), 'Table'[ID] = EARLIER ( 'Table'[ID] ) ),
'Table'[Amount]
) < 0.8 * _P
)
),
"between 80 to 100%",
CALCULATE (
DISTINCTCOUNT ( 'Table'[ID] ),
FILTER (
ALL ( 'Table' ),
SUMX (
FILTER ( ALL ( 'Table' ), 'Table'[ID] = EARLIER ( 'Table'[ID] ) ),
'Table'[Amount]
) >= 0.8 * _P
&& SUMX (
FILTER ( ALL ( 'Table' ), 'Table'[ID] = EARLIER ( 'Table'[ID] ) ),
'Table'[Amount]
) <= _P
)
),
"over 100%",
CALCULATE (
DISTINCTCOUNT ( 'Table'[ID] ),
FILTER (
ALL ( 'Table' ),
SUMX (
FILTER ( ALL ( 'Table' ), 'Table'[ID] = EARLIER ( 'Table'[ID] ) ),
'Table'[Amount]
) > _P
)
)
)
3.In the 100% Stacked Bar Chart, put the Section column in the Legend, the measure in the Values, get the correct result.
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
According to your description, here's my solution.
1.Create a section table containing the four sections.
2.Create a measure.
Measure =
VAR _P =
SELECTEDVALUE ( Parameter[Parameter] )
RETURN
SWITCH (
MAX ( 'Section Table'[Section] ),
"below 50%",
CALCULATE (
DISTINCTCOUNT ( 'Table'[ID] ),
FILTER (
ALL ( 'Table' ),
SUMX (
FILTER ( ALL ( 'Table' ), 'Table'[ID] = EARLIER ( 'Table'[ID] ) ),
'Table'[Amount]
) < 0.5 * _P
)
),
"between 50 to 80%",
CALCULATE (
DISTINCTCOUNT ( 'Table'[ID] ),
FILTER (
ALL ( 'Table' ),
SUMX (
FILTER ( ALL ( 'Table' ), 'Table'[ID] = EARLIER ( 'Table'[ID] ) ),
'Table'[Amount]
) >= 0.5 * _P
&& SUMX (
FILTER ( ALL ( 'Table' ), 'Table'[ID] = EARLIER ( 'Table'[ID] ) ),
'Table'[Amount]
) < 0.8 * _P
)
),
"between 80 to 100%",
CALCULATE (
DISTINCTCOUNT ( 'Table'[ID] ),
FILTER (
ALL ( 'Table' ),
SUMX (
FILTER ( ALL ( 'Table' ), 'Table'[ID] = EARLIER ( 'Table'[ID] ) ),
'Table'[Amount]
) >= 0.8 * _P
&& SUMX (
FILTER ( ALL ( 'Table' ), 'Table'[ID] = EARLIER ( 'Table'[ID] ) ),
'Table'[Amount]
) <= _P
)
),
"over 100%",
CALCULATE (
DISTINCTCOUNT ( 'Table'[ID] ),
FILTER (
ALL ( 'Table' ),
SUMX (
FILTER ( ALL ( 'Table' ), 'Table'[ID] = EARLIER ( 'Table'[ID] ) ),
'Table'[Amount]
) > _P
)
)
)
3.In the 100% Stacked Bar Chart, put the Section column in the Legend, the measure in the Values, get the correct result.
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |