Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a bar graph that shows the revenue per customer. In this page, I have several slices. Thus, the user can filter per customer, per market, per fiscal year or business type.
In addition, he can click in the bar and drill through for more detailed customer information.
The problem is that for small values of revenue, I have no bar. Thus, the user can not drill through for more customer information.
I was thinking that maybe if I was able to add a measure slicer, like: show me customers with revenue from 10 to 20 millions only, the axis would adjust automatically, allowing the user could to see the bar and drill through for more customer details.
I would like to make it easy for the user to filter, to create intervals such as:
Example of Filter Revenue:
0 - 5 Millions
5 Millions - 10 Millions
10 Millions - 20 Millions
>20 Millions
However, I don't know how to do that. My current slicer is the default one.
If you have a different idea on how to solve this, how to make the drill through appear, I would also be grateful.
Thanks in advance!
Solved! Go to Solution.
Hi, @jessicarocha ;
You could enter a table ,then create a flag measure.
1.enter table as slicer.
2.create a flag measure.
flag =
VAR _flag =
SWITCH (
MAX ( 'Slicer'[Filter] ),
"0 - 5 Millions",
IF ( SUM ( 'Table'[Revenue] ) >= 0 && SUM ( 'Table'[Revenue] ) < 5000000, 1 ),
"5 Millions - 10 Millions",
IF (
SUM ( 'Table'[Revenue] ) >= 5000000
&& SUM ( 'Table'[Revenue] ) < 10000000,
1
),
"10 Millions - 20 Millions",
IF (
SUM ( 'Table'[Revenue] ) >= 10000000
&& SUM ( 'Table'[Revenue] ) < 20000000,
1
),
">20 Millions", IF ( SUM ( 'Table'[Revenue] ) >= 20000000, 1 )
)
RETURN
IF ( ISFILTERED ( Slicer ), _flag, 1 )
3.apply flag measure into bar visual.
The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @jessicarocha ;
Hello, you need to check whether the fields in this Slicer table are consistent with the fields in the Flag Measure. Pay special attention to the Spaces, such as the front and back Spaces
Sometimes the space is not easy to find, you can use another auxiliary measure to verify whether the consistency, such as:
check =IF ( MAX ( 'Slicer'[Filter] ) = "0 - 5 Millions", 1, 0 )
then put it into card and select the "0 - 5 Millions", if card is always show "0", Then you can determine that the field does not match and you need to modify it. if not right , can you share the slicer table screenshot?
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-yalanwu-msft thank you so much. That was exactly my problem. All fixed now.
I really appreciate the help!
Kind regards,
Jéssica
Hi, @jessicarocha ;
You could enter a table ,then create a flag measure.
1.enter table as slicer.
2.create a flag measure.
flag =
VAR _flag =
SWITCH (
MAX ( 'Slicer'[Filter] ),
"0 - 5 Millions",
IF ( SUM ( 'Table'[Revenue] ) >= 0 && SUM ( 'Table'[Revenue] ) < 5000000, 1 ),
"5 Millions - 10 Millions",
IF (
SUM ( 'Table'[Revenue] ) >= 5000000
&& SUM ( 'Table'[Revenue] ) < 10000000,
1
),
"10 Millions - 20 Millions",
IF (
SUM ( 'Table'[Revenue] ) >= 10000000
&& SUM ( 'Table'[Revenue] ) < 20000000,
1
),
">20 Millions", IF ( SUM ( 'Table'[Revenue] ) >= 20000000, 1 )
)
RETURN
IF ( ISFILTERED ( Slicer ), _flag, 1 )
3.apply flag measure into bar visual.
The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-yalanwu-msft thank you so much for the detailed answer and the pbix file. This is exactly what I am trying to achieve.
I followed your steps but something is not right for me. Only one option in the slicer is working (>20 Millions). When I select any other, no data appears. I can't figure it out what is wrong. Do you have any idea?
Best regards,
Jéssica
@jessicarocha , You need to binning or segmentation using an independent table
Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1...
Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k
Binning
https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-grouping-and-binning
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |