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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. 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
amitchandak
Super User
Super User

@mohan_g_das1986 , see if these can help

https://www.daxpatterns.com/dynamic-segmentation/
https://www.daxpatterns.com/static-segmentation/
https://www.poweredsolutions.co/2020/01/11/dax-vs-power-query-static-segmentation-in-power-bi-dax-po...
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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