cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Undercover95
Helper I
Helper I

Incorrect Average Location Unit Margin

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:

 

Undercover95_0-1598629214087.png

 

With Material/Segment Filter selected:

Undercover95_1-1598629483463.png


With Both filters selected:

Undercover95_2-1598629542991.png

 

 



1 ACCEPTED SOLUTION

Hi @Undercover95 ,

 

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

 

in1.jpg

 

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

 

in2.jpg

 

in3.jpg

 

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.

View solution in original post

4 REPLIES 4
v-zhenbw-msft
Community Support
Community Support

Hi @Undercover95 ,

 

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.

 

in1.jpg

 

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')))

 

in2.jpg

 

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.

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  

Hi @Undercover95 ,

 

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

 

in1.jpg

 

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

 

in2.jpg

 

in3.jpg

 

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.

View solution in original post

Pragati11
Super User
Super User

Hi @Undercover95 ,

 

Is it possible to share some sample data here?

 

Thanks,

Pragati


My Blog: Data Vibe
Connect: LinkedIn
Connect: Twitter






If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors