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

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.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

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

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

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