How to get average of 5 different sums?

[Anmolgan]

I have a measure that I use to calculate my gross margin this measure goes like this:

Gross Margin = ZSD_MAT_MARGIN_Q003[Gross Material Margin]-SUM('Condtion Data'[Rates])-ZSD_MAT_MARGIN_Q003[F.O.R Freight] - [Z210 Coupon]- sum('Month Wise Royalty'[Royalty]) - SUM(Commision[Rate (Comission)])

Now I have 5 different groups for which I create Gross Margin differently, now I want to see average of all those 5 different groups so that I can plot that inside my linechart, now my linechart should be build in such a way so that it can show individual gross margins, and also when I do not use any filters it shows average values of all those 5 different groups is that possible?

[v-gizhi-msft]

Hi,

According to your description, I used your pbix file to test and made some changes to your measures:

Here is one changed measure as the example:

Gross Margin Measure =

IF (

    CALCULATE ( COUNTROWS ( Mapping ), ALLSELECTED ( Mapping ) )

        = CALCULATE ( COUNTROWS ( Mapping ), ALL ( Mapping ) ),

    AVERAGEX (

        SUMMARIZE (

            Mapping,

            Mapping[Sales Group],

            "Net", CALCULATE (

                [Gross Margin],

                FILTER (

                    'ZSD_MAT_MARGIN_Q003',

                    'ZSD_MAT_MARGIN_Q003'[Calendar Year/Month.Calendar Year/Month Level 01]

                        IN SELECTCOLUMNS (

                            FILTERS ( 'Month Wise Royalty'[Month/Year] ),

                            "MonthYear", UPPER ( [Month/Year] )

                        )

                )

            )

        ),

        [Net]

    ),

    CALCULATE (

        [Gross Margin],

        FILTER (

            'ZSD_MAT_MARGIN_Q003',

            'ZSD_MAT_MARGIN_Q003'[Calendar Year/Month.Calendar Year/Month Level 01]

                IN SELECTCOLUMNS (

                    FILTERS ( 'Month Wise Royalty'[Month/Year] ),

                    "MonthYear", UPPER ( [Month/Year] )

                )

        )

    )

)

And the other three measures should be changed as the same with above.

The result shows:

1)when not choosing any Sales Group slicer value:

2)when choosing one value in Sales Group slicer:

Here is the changed pbix file you shared:

pbix 

Best Regards,

Giotto Zhi

[Anmolgan]

@Mariusz  Thanks for the response, but this is something that is giving me incorrect values, plus I was able to create some measures that gives me weighted averages for Rates, Commission, Royalty, I had created below measures for each of the rates here:

Royalty weighted =

VAR __CATEGORY_VALUES = VALUES('ZSD_MAT_MARGIN_Q003'[Customer Group])

RETURN

    DIVIDE(

        SUMX(

            KEEPFILTERS(__CATEGORY_VALUES),

            CALCULATE(SUM('Month Wise Royalty'[Royalty]) * [Sales Volume KL Measure])

        ),

        SUMX(

            KEEPFILTERS(__CATEGORY_VALUES),

            CALCULATE([Sales Volume KL Measure])

        )

    )

Likewise I had built similar measures, and then am using all of this inside below measure:

Gross Margin Weighted Average = [Gross Material Margin (₹/ltr.)]- [Royalty weighted] - [F.O.R Freight (₹/ltr.)] - [Weighted Rates] - [Rates (Comission) Weighted] - [Z210 Coupon weighted by Sales Volume KL Measure per Customer Group]

And data validation is ok for the results which comes, my only problem now is the page 1 line chart where I am using Month/Year column (Table- Month Wise Royalty) from a spreadsheet, now this spreadsheet contains different customer groups and there month wise royalty, I also have a calender year column in my Master Query that is ZSD, is there any measure that I can create that gives me correct results as the above measure is giving me in the line chart but only using with Calender Year Column?

[Anmolgan]

@Mariusz 

Here is my Pbix file: https://ifitech-my.sharepoint.com/:u:/g/personal/anmol_ganju_ifi_tech/EfU8FgWYavtCm8XmbWaM_iIB-rku-W...

And the spreadsheet that I have: https://ifitech-my.sharepoint.com/:x:/g/personal/anmol_ganju_ifi_tech/EbG9yQi0NQFCvpcZS1qtmlcB4uAoe9...

[Mariusz]

Hi @Anmolgan 

Can we have some data sample?

Thanks

Mariusz

[Anmolgan]

@Mariusz 

Hi here is the report link https://ifitech-my.sharepoint.com/:u:/g/personal/anmol_ganju_ifi_tech/EWTxOwU-fZNMrfjuVvLkj3MBBkH-Vl...

I need to see 5 sales group overall average in gross margin, for now individual calculation of gross margin is correct, but am not sure how can I calculate the average and display inside the same line chart which is in page1?