Incorrect Average Location Unit Margin

[Anonymous]

Hello,

I am trying to bring a visual of comparison between Volume (Measure), Unit Margin (Measure) & Location Unit Margin (Column).

This location unit margin should automatically change based upon the location I select, or the Product/Material type & Segment.

If my formula worked properly, my blue line (Loc Unit Margin) should ideally be a straight line as it is an Average. However if i do not select any material or Segment in the slicer as below, then the line goes "Wacky". However if i need a straight line, i need to select both Material & Segment to make the line straight.

Requirement/Help needed:

How should the formula look like if there is no filter of material/segment selected to ensure i get that line straight?

I hope i was able to explain my problem & requirement correctly. Please help me out as I have been breaking my head for 2 weeks now! Thank you!

No Filter selections:

With Material/Segment Filter selected:

With Both filters selected:

[v-zhenbw-msft]

Hi @Anonymous ,

Do you want to display a horizontal average?

If yes, we can use average line or create a measure to meet your requirement.

1. Create a clustered column chart, then add an average line.

2. Create a measure, then create a line and column chart and put the measure in line values.

Average = 
DIVIDE(
    CALCULATE(SUM('Table'[sales]),ALL('Table')),
    CALCULATE(DISTINCTCOUNT('Table'[Group]),ALL('Table')))

If it doesn’t meet your requirement, could you please provide a mockup sample based on fake data or describe the fields of each tables and the relations between tables simply?

It will be helpful if you can show us the exact expected result based on the tables.

Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

BTW, pbix as attached.

[Anonymous]

Hello,

Thanks for replying quickly. I have attached a sample data in Onedrive via Excel, please use that. I tried the formula you mentione which didnt work for me. I have also put a simple pivot of the data which mentions the different Avg Unit Margin.

For Eg: If i filter for Customer Segment as GA, then my Average line should be 24.56 and I should be able to compare it with other customers in the same location. Or if i filter only for Material 1, the Avg Unit Margin is 12.47. And i should be able to compare it with other customers buying the same material in that location.

Excel Raw Data  

[v-zhenbw-msft · Accepted solution]

Hi @Anonymous ,

Do you want to display the dark blue line or orange line?

If you want to show the dark blue line, please refer the following measure,

Measure 1 =
VAR loc =
    MAX ( Data[MIS Location] )
VAR segment =
    MAX ( Data[Customer Segment] )
VAR Ma =
    MAX ( Data[Material Group] )
RETURN
    DIVIDE (
        CALCULATE (
            SUM ( 'Data'[Actual] ),
            FILTER (
                'Data',
                'Data'[Category] = "Margin"
                    && Data[MIS Location] = loc
                    && Data[Customer Segment] = segment
                    && Data[Material Group] = Ma
            )
        ),
        CALCULATE (
            SUM ( 'Data'[Actual] ),
            FILTER (
                'Data',
                'Data'[Category] = "Volume"
                    && Data[MIS Location] = loc
                    && Data[Customer Segment] = segment
                    && Data[Material Group] = Ma
            )
        )
    ) * 100

If you want to show the orange line, please refer the following measure.

Measure 2 =
VAR loc =
    MAX ( Data[MIS Location] )
VAR segment =
    MAX ( Data[Customer Segment] )
VAR Ma =
    MAX ( Data[Material Group] )
RETURN
    DIVIDE (
        CALCULATE (
            SUM ( 'Data'[Actual] ),
            FILTER ( ALLSELECTED ( 'Data' ), 'Data'[Category] = "Margin" )
        ),
        CALCULATE (
            SUM ( 'Data'[Actual] ),
            FILTER ( ALLSELECTED ( 'Data' ), 'Data'[Category] = "Volume" )
        )
    ) * 100

If it doesn’t meet your requirement, could you please show us the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

BTW, pbix as attached.

[Pragati11]

Hi @Anonymous ,

Is it possible to share some sample data here?

Thanks,

Pragati