cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

New Member

## Need to apply productx calculation after applying a filter based on a measure value

Hi Team,

I have a requirement to create  Product wise  Sale%  based on Itemwise Sale% under each product .

Please see the below Screenshot . Actual , Planned and % Sales are measures

Now I have a Range slicer which shows value 1 to 100 . I can change this value when needed . For now, I selected  value as  50

Now, I am finding out  % based on slicer value by calculating Minimum and maximum Item value of actual and Planned  for each product and multiply with this slicer value

example

I am creating a Flag, If Planned< % Based on Slicer Input then updating it as 0(zero) this record has to be excluded from Productwise Sales% calculation

Productwise % Sales= product of %sales of each item

ex. For Product A Sale% = 100*100*50  . 75 excluded for calculation

Product B %Sales= 25*100*75 . 0 excluded for calculation.Similary for Product C Sales %= 10*0*100 , Product D Sale%= 0

I Hope you got the requirement . Appreciate your valuable inputs

1 ACCEPTED SOLUTION
Super User

@dk_powerbiuser1 So, maybe this:

``````  VAR __Product = MAX('Table'[Product])
VAR __Table =
FILTER(
SUMMARIZE(
FILTER( ALL('Table'), [Product] = __Product ),
[Product],
[Item],
"__%Sales", [%Sales],
"__Flag", [Flag]
),
[__Flag] > 0
)
VAR __Result = PRODUCTX( __Table, [__%Sales] )
RETURN
__Result``````

Basically, the measure aggregation pattern. This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Super User

@dk_powerbiuser1 So, maybe this:

``````  VAR __Product = MAX('Table'[Product])
VAR __Table =
FILTER(
SUMMARIZE(
FILTER( ALL('Table'), [Product] = __Product ),
[Product],
[Item],
"__%Sales", [%Sales],
"__Flag", [Flag]
),
[__Flag] > 0
)
VAR __Result = PRODUCTX( __Table, [__%Sales] )
RETURN
__Result``````

Basically, the measure aggregation pattern. This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### 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