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

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

Reply
MadhumithaV_26
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 CategoryProduct Sub CategorySales % (Calculated measure)
A126.7%
A226.8%
A329.3%
B430.4%
B531.8%
B634.6%
B738.6%
C841.1%
C963.4%

 

Thanks in advance !!

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

 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.

View solution in original post

1 REPLY 1
v-jingzhang
Community Support
Community Support

 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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.