cancel
Showing results 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.

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
Super User

@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:

 Slab Sort Min Max -41% -1000% 1 -0.41 -10 -21% -040% 2 -0.21 -0.4 00% -020% 3 0 -0.2 +00% 020% 4 0 0.2 +21% +040% 5 0.21 0.4 +41% +1000% 6 0.41 10

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

@mohan_g_das1986 , see if these can help

Super User

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
Helper III

Super User

@mohan_g_das1986

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

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.

Community Champion

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

Super User

@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:

 Slab Sort Min Max -41% -1000% 1 -0.41 -10 -21% -040% 2 -0.21 -0.4 00% -020% 3 0 -0.2 +00% 020% 4 0 0.2 +21% +040% 5 0.21 0.4 +41% +1000% 6 0.41 10

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

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

Super User

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
Helper III

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)

 ID Month Reseller_Name Product From Disty Sales Out Ratio (Measure created) 3-H-3899 May Reseller_Name1 Product1 900 3-G-3900 May Reseller_Name2 Product2 100 3-H-3900 May Reseller_Name3 Product3 500 10 -98% 3-G-3901 May Reseller_Name4 Product4 1700 300 -82% 3-H-3901 May Reseller_Name5 Product4 900 1100 22% 3-G-3902 May Reseller_Name6 Product6 856 3-H-3902 May Reseller_Name7 Product1 5000 80 -98% 3-G-3903 May Reseller_Name8 Product2 3400 3000 -12% 3-H-3903 May Reseller_Name9 Product5 300 159 -47% 3-H-3900 May Reseller_Name3 Product1 5000 10 -100%

Announcements

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

Power BI Monthly Update - April 2024

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

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors