Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Surekha_PM
Helper II
Helper II

Show Medium, Low and High data representation

I have a data set with multiple columns. I need to show three different condition product lists in three different cards (one below the other).
First, if the sales value is below the selected range, then all the product listed parallel to that will show in the low card 
Second, if the sales value is between the selected range, then all the product listed parallel to that will show in the midcard
Third, if the sales value is above the selected range, then all the products listed parallel to that will show in the high card.
So, basically, the user will change the sales value in the slicer, and then the card should change accordingly. 
For example, if a user is selecting 100 to 140, then all the products with values below 100 should appear in the low card, 100 to 140 value products should appear in the mid card and any product value greater than 140 should appear in the third high card.

Here we have two challenges. First, create a formula for all three conditions. Second, to create a slicer to filter out the values based on a range. 

Any suggestions from anyone is really appreciated. Thanks!

 

9 REPLIES 9
Surekha_PM
Helper II
Helper II

Hi @Surekha_PM,

It seems like I can't view the shared link, can you please move this to another way sharing?

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Surekha_PM
Helper II
Helper II

Hello,

I am attaching a PBIX file for better understanding. In the attached file, you can see the format that I want to create. Basically, I want to show the category name based on CTR and clicks. And the user should have the freedom to change both the metrics to see the category list. For example, if a user wants to see which categories are listed under clicks below 500 and CTR below 0.5%, they can see the result in the low box.

@Jihwan_Kim I tried your formula and it is working but I am still seeing duplicate categories, which I am not able to remove. 

PBIX link - https://drive.google.com/file/d/13FHpVk7NVLMmYeHmLGl3zbiSHN1VH--k/view?usp=drive_link

Hope this sample file helps.

Thanks!
Regards,
Surekha

v-shex-msft
Community Support
Community Support

Hi @Surekha_PM ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Ashish_Mathur
Super User
Super User

Hi,

Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Surekha_PM
Helper II
Helper II

Also, if I am filtering 500 to 3000, the product of 500 value is showing in the low as well as med card. Same way 3000 value product is showing in med as well as high. 
Can we do it like this: in the med card, the product should reflect the 501 value, and in the high card, it should be 3001 and above?

 

Hi,

In my sample, I cannot filter to show between 500 to 3000.

If you are using different sample pbix file, please kindly share the link.

Or, please try fixing >= to > in your formula.

Thank you.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

Surekha_PM
Helper II
Helper II

Hi @Jihwan_Kim ,

Thank for your help. This is showing the result. But it is showing duplicate data where I need unique names. Also, Can we show it one below other rather than the default visual where it is separating with comma.

For example, currently it is showing as A,B,C,D. 
I want it to show like below
A
B
C
D

Thanks!

Jihwan_Kim
Super User
Super User

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

I tried to create a slicer table (Calculated Table) by using the formula below.

 

CC_slicer = 
VAR _min =
    MIN ( sales[sales] )
VAR _max =
    MAX ( sales[sales] )
RETURN
    SELECTCOLUMNS ( GENERATESERIES ( _min, _max, 1 ), "sales_range", [Value] )

 

 

Jihwan_Kim_1-1735879950562.png

 

 

Jihwan_Kim_0-1735879935162.png

 

product list below selection: = 
VAR _minselect =
    MIN ( CC_slicer[sales_range] )
VAR _t =
    FILTER ( sales, CALCULATE ( SUM ( sales[sales] ) ) < _minselect )
RETURN
    CONCATENATEX ( _t, sales[product], ", " )

 

product list between selection: = 
VAR _minselect =
    MIN ( CC_slicer[sales_range] )
VAR _maxselect =
    MAX ( CC_slicer[sales_range] )    
VAR _t =
    FILTER ( sales, CALCULATE ( SUM ( sales[sales] ) ) >= _minselect && CALCULATE ( SUM ( sales[sales] ) ) <= _maxselect )
RETURN
    CONCATENATEX ( _t, sales[product], ", " )

 

product list above selection: = 
VAR _maxselect =
    MAX ( CC_slicer[sales_range] )
VAR _t =
    FILTER ( sales, CALCULATE ( SUM ( sales[sales] ) ) > _maxselect )
RETURN
    CONCATENATEX ( _t, sales[product], ", " )

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.