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

[MadhumithaV_26]

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

[v-jingzhang · Accepted solution]

 Hi @MadhumithaV_26 

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.