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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tran001403
Regular Visitor

Need to be able to apply a Measure as a filter to a % of Total Calculated Measure

 My request: For each store LOCATION, what percentage of lines were fulfilled by each ORDER TYPE, but only count if the result of my calculated measure STOCKED>3

Basically, I have multiple store locations and 600,000 lines of sales data.
I am able to calculate the Order Type Percentage by calculating what % of that location's rows have that order type, using a CountA formula and a matrix table.

Stocked Fill % = counta('Fill Detail'[Order Type])/calculate(counta('Fill Detail'[Order Type]),ALLSELECTED('Fill Detail'))

 

example sales lines contain:

LOCATION-ORDERTYPE-STOCKED(measure)
1 DSO 4
2 DSO 5
1 EMERG 1
1 DSO 2
1 EMERG 5
2 DSO 5

My dilemma is that I have no way to apply a filter using my "Stocked" measure, which is a fairly complex rolling 12 month calculation that can't be added at the table level.
I would appreciate help for how to add an additional filter to my formula, but note, it's with a calculated measure, not a table field.
I tried just applying a visual level filter but it didn't work since my filter was a measure.
Any ideas?

 

3 REPLIES 3
v-juanli-msft
Community Support
Community Support

Hi @tran001403

My request: For each store LOCATION, what percentage of lines were fulfilled by each ORDER TYPE, but only count if the result of my calculated measure STOCKED>3.

 

Does “the result of my calculated measure STOCKED” refer to “the percentage of lines were fulfilled by each ORDER for each store LOCATION”?

 

If it is, you can edit it in Filters->Visual level filter->show items when the value is more than 3, or modify the original formula as below:

IF(counta('Fill Detail'[Order Type])/calculate(counta('Fill Detail'[Order Type]),ALLSELECTED('Fill Detail'))>3, counta('Fill Detail'[Order Type])/calculate(counta('Fill Detail'[Order Type]),ALLSELECTED('Fill Detail')))

 

If “the percentage of lines were fulfilled by each ORDER for each store LOCATION” should be obtained by another measure and be filter by "Stocked" measure, you could write the "Stocked" measure as a condition in the formula, if you have any problem with this, please share me with the formula you want to create.

 

 

Best Regards

Maggie

Thank you so much for your willingness to help.

 

To answer your question, I believe it is the 2nd scenario.

STOCKED is a separate measure that should be a conditional filter on this FILL% measure/formula.

I'm just not sure how to best do that and not sure if the issue is with my formula or just the visual itself.

 

To explain further, Our stocking criteria is parts with 3 or more hits annually, so I only want those rows included in the FILL% calculation. So first, I did a STOCKED measure for a running total that calculates for that order date, at that point in time, how many Hits did the part have in the past year. Calculate(sum('Fill Detail'[Hit]),DATESINPERIOD('Fill Detail'[Order Date],Max('Fill Detail'[Order Date]),-365,DAY)). I'm not sure if there's any issue there, but that appears to be giving me accurate results when viewing the individual rows on a table.

 

So the next step is: I want a measure for Fill % (percentage of lines fufilled by each order type by location) but I need to exclude any rows from the count if the STOCKED measure isn't >3. So yes, just a conditional filter.

 

I tried this: If([Stocked]>=3,counta('Fill Detail'[OrderType])/calculate(counta('Fill Detail'[Order Type]),ALLSELECTED('Fill Detail'))).

 

Two issues: 

-When results are displayed in a table visual, it is calculating it as a percent of grand total of all rows, I need to somehow include group-by the LOCATION code into that formula. 

 

-On the visual stacked bar graph, I am able to add an axis for location, so it actually appears like it's correctly displaying each location's % separate (not % of grand total); On that visual, I have Location as the Axis, Order Type as the Legend, Fill% as the value. Everything looks fine, except it is not excluding parts that don't meet the conditional filter for STOCKED, which is what I need it to do. I tried applying a Visual level filter as well as the above filter in the Fill% but it had no impact.

 

Please let me know any suggestions or if more info is needed.

Thanks

Hi @tran001403

Could you share me a example dataset or pbix for better analysis?

 

Best regards

Maggie

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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