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.

Frequent Visitor

How to calculate the average of Sales% that's lies within the ranges?

Hi All,

For Example: Consider the below table, we need to create three measures where it calculates the average of Sales % that's within the below ranges.

1. First measure - Calculate Average of sales % when sales % <= 30%

Eg: 26.7% + 26.8% + 29.3% / 3 = 27.6%

2. Second measure - Calculate Average of sales % when sales % > 30% and <= 40%

Eg: 30.4% + 31.8% + 34.6% + 38.6% / 4 = 33.85%

3. Thrid measure - Calculate Average of sales % when sales % > 40% and <= 100%

Eg: 41.4% + 63.4% / 2 = 52.4%

Note: Sales% is a calculated measure

We need to use all these 3 measures in 3 different card visual. Can anybody help me with the logic here?

 Product Category Product Sub Category Sales % (Calculated measure) A 1 26.7% A 2 26.8% A 3 29.3% B 4 30.4% B 5 31.8% B 6 34.6% B 7 38.6% C 8 41.1% C 9 63.4%

Thanks in advance !!

1 ACCEPTED SOLUTION
Community Support

You can try these measures

``````First Measure =
VAR _table =
SUMMARIZE (
'Table',
'Table'[Product Category],
'Table'[Product Sub Category],
"Sales% value", [Sales %]
)
RETURN
AVERAGEX ( FILTER ( _table, [Sales% value] <= 0.3 ), [Sales% value] )
``````
``````Second Measure =
VAR _table =
SUMMARIZE (
'Table',
'Table'[Product Category],
'Table'[Product Sub Category],
"Sales% value", [Sales %]
)
RETURN
AVERAGEX (
FILTER ( _table, [Sales% value] > 0.3 && [Sales% value] <= 0.4 ),
[Sales% value]
)
``````
``````Third Measure =
VAR _table =
SUMMARIZE (
'Table',
'Table'[Product Category],
'Table'[Product Sub Category],
"Sales% value", [Sales %]
)
RETURN
AVERAGEX (
FILTER ( _table, [Sales% value] > 0.4 && [Sales% value] <= 1 ),
[Sales% value]
)
``````

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

Community Support

You can try these measures

``````First Measure =
VAR _table =
SUMMARIZE (
'Table',
'Table'[Product Category],
'Table'[Product Sub Category],
"Sales% value", [Sales %]
)
RETURN
AVERAGEX ( FILTER ( _table, [Sales% value] <= 0.3 ), [Sales% value] )
``````
``````Second Measure =
VAR _table =
SUMMARIZE (
'Table',
'Table'[Product Category],
'Table'[Product Sub Category],
"Sales% value", [Sales %]
)
RETURN
AVERAGEX (
FILTER ( _table, [Sales% value] > 0.3 && [Sales% value] <= 0.4 ),
[Sales% value]
)
``````
``````Third Measure =
VAR _table =
SUMMARIZE (
'Table',
'Table'[Product Category],
'Table'[Product Sub Category],
"Sales% value", [Sales %]
)
RETURN
AVERAGEX (
FILTER ( _table, [Sales% value] > 0.4 && [Sales% value] <= 1 ),
[Sales% value]
)
``````

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

Announcements

New forum boards available in Real-Time Intelligence.

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

Power BI Monthly Update - May 2024

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

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors