cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors