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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
mohan_g_das1986
Helper III
Helper III

Create Segments for measure which is in %

I have a table of 50 accounts & their products. These accounts buy products from distributors & sell to end customers. i have to create a dashboard which products are selling good & whatnot.

TotalSalesout = CALCULATE(SUM(PSS_Data[SalesOut]),PSS_Data[SalesType] = "Sales Out")
TotalSTUnits = CALCULATE(SUM(PSS_Data[Units]),PSS_Data[SalesType] = "Sell Thru")
Ratio Units = CALCULATE((DIVIDE([TotalSalesoutUnit],[TotalSTUnits])-1),PSS_Data[Report_Flag] = 1) ---> Percentage (%).
output --> -500% to +500%. (+ or -20% --> Good, + or - 40% - Ok & + or - >40% is BAD)
Now I have create a slicer button for the users to with about group. When they click they should see the account, product based on ration selection.
 
Can someone help me?? Quickly
1 ACCEPTED SOLUTION

@mohan_g_das1986 

Replace Column with this measure:

Ratio_units = 
DIVIDE(
    MAX('Table'[Sales Out]),
    MAX('Table'[From Disty])
)

 
Modified Measure:

Measure = 
VAR R = [Ratio_units]
VAR BRACKET_MAX = SELECTEDVALUE('Ration Bracket'[Max])
VAR BRACKET_MIN = SELECTEDVALUE('Ration Bracket'[Min])
RETURN
IF( 
    ISFILTERED('Ration Bracket'[Slab]),
    IF(
        R>=BRACKET_MIN &&
        R<BRACKET_MAX,
        1,
        0
    ),
    1
)


Ration Bracket Table:

 

SlabSortMinMax
-41% -1000%1-0.41-10
-21% -040%2-0.21-0.4
00% -020%30-0.2
+00% 020%400.2
+21% +040%50.210.4
+41% +1000%60.4110



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

10 REPLIES 10
Fowmy
Super User
Super User

@mohan_g_das1986 

 

Can you share a screenshot of your Slicer showing all values? the brackets aren't clear enough.
Also, Insert a Table visual and drop Product and other fields that you need.

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

mohan_g_das1986_0-1594471455316.png

 

@mohan_g_das1986 

Please check this file: https://1drv.ms/u/s!AmoScH5srsIYgYFgiIhao7Pl4_yk6w?e=ADQL32

Mark this reply as a solution if I answered your question

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Hi, Thanks for the workaround; guess we are almost there. Ratio units (is a measure), but you have made it as a column.

can you create a measure Ratio_units= Divide(Sales out, From Disty).  Then see if this works.

 

Need your help, please

 

mohan_g_das1986_0-1594485425387.png

 

Hi @mohan_g_das1986 ,

 

var r = Out Ratio  -- measure you have created for Ratio

 

Just put the measure name and it should work.

 

Also in measure 2 replace the Table[Col Name] with the Measure created for ratio. It will filter and give you the same results.

 

Regards,

HN

@mohan_g_das1986 

Replace Column with this measure:

Ratio_units = 
DIVIDE(
    MAX('Table'[Sales Out]),
    MAX('Table'[From Disty])
)

 
Modified Measure:

Measure = 
VAR R = [Ratio_units]
VAR BRACKET_MAX = SELECTEDVALUE('Ration Bracket'[Max])
VAR BRACKET_MIN = SELECTEDVALUE('Ration Bracket'[Min])
RETURN
IF( 
    ISFILTERED('Ration Bracket'[Slab]),
    IF(
        R>=BRACKET_MIN &&
        R<BRACKET_MAX,
        1,
        0
    ),
    1
)


Ration Bracket Table:

 

SlabSortMinMax
-41% -1000%1-0.41-10
-21% -040%2-0.21-0.4
00% -020%30-0.2
+00% 020%400.2
+21% +040%50.210.4
+41% +1000%60.4110



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Hi - Thanks so much for this solution has worked. Appreciate you help

You are welcome!


If you are satisfied with my answer, please mark it as a solution so others can easily find it.

APPRECIATE KUDOS!

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

This is how the data is in my table;  Ratio is a measure (Salesout/From Dity)-1.

I want to create a filter checklist / Slicer button - using the ratio value. (0 to 20%) - 1 choice, (21% to 40%) 2nd choice & 3rd >=41%. Similarly 0 to -20, -21 to -40 & < -41%.

 

Based on the selection I wanted the user to see their data (Reseller_Name, product & units)

 

IDMonthReseller_NameProductFrom DistySales OutRatio (Measure created)
3-H-3899MayReseller_Name1Product1900  
3-G-3900MayReseller_Name2Product2100  
3-H-3900MayReseller_Name3Product350010-98%
3-G-3901MayReseller_Name4Product41700300-82%
3-H-3901MayReseller_Name5Product4900110022%
3-G-3902MayReseller_Name6Product6856  
3-H-3902MayReseller_Name7Product1500080-98%
3-G-3903MayReseller_Name8Product234003000-12%
3-H-3903MayReseller_Name9Product5300159-47%
3-H-3900MayReseller_Name3Product1500010-100%

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.