Average using filter from Visual

[Sporty583]

Dear All,

I am very new to Power BI and have now arrived at a point where I need your help.

My issue is the following:

I have a table with products (column "Pre Product") and their amount used (column "Amount used") to fulfill an order (column "order").

Pre Product	Order	Amount used
A	1	100
B	1	200
C	1	200
D	1	100
A	2	200
C	2	200
D	2	200
A	3	300
B	3	150
C	3	150
A	4	50
B	4	50
C	4	250
D	4	250

In another table I have the definition of the final products of the orders. Both tables are cross referenced so I can find the Final products for the orders.

Order	Final Product
1	TypeX
2	TypeY
3	TypeX
4	TypeY

For example: Order 1 is an order for Final Product TypeX. For order 1 there was an amount of 100 of pre product A used.

In PowerBI I have a visual table for the Final Product:

Final Product

TypeX

TypeY

Finally what I am looking for is the average of each pre product used for all orders of a Final product.

Example: I select TypeX in my Final Product visual Table.

The result I am looking for should look like that:

Pre Product	Average used per Order
A	200
B	175
C	175
D	50

This works well as long as a pre product is used in every order. But if a pre product is not used in every order the average PowerBI provides is just the average for the orders in which the product was used.

In the example that means the result for Pre product D would be 100, because it was only used in order 1. I want the result to be 50, because there were 2 orders of final product TypeX produced.

I hope the description is detailled enough so that you might help a beginner to understand more of the program.

Thanks and best regards

Christian

[johnt75 · Accepted solution]

try changing the totalOrders line to

var totalOrders = CALCULATE( COUNTROWS('Orders'), REMOVEFILTERS('Sales'[Pre product]) )

[Sporty583]

Thanks for your help...

This measuer worked for me:

Measure = 

var einsatzmenge = calculate(sum(Sheet1[Einsatzmenge [t]]]))

var anzahlfa = calculate(DISTINCTCOUNT(Sheet1[Auftrag]),REMOVEFILTERS(Sheet1[Material]))

return DIVIDE(einsatzmenge,anzahlfa)

@v-rongtiep-msft Thanks for your reply. I have some other issues similar to my original one where I am sure that your solution will help.

[v-rongtiep-msft · Accepted solution]

Hi @Sporty583 ,

Please refer to my pbix file to see if it helps you.

Create a column firstly.

Column =
CALCULATE (
    COUNT ( 'Table'[Order] ),
    FILTER (
        ( 'Table' ),
        'Table'[Final Product] = EARLIER ( 'Table'[Final Product] )
    )
)

Then create measures.

——final =
MAX ( 'Table'[Final Product] )

Measure =
VAR s_all =
    CALCULATE (
        SUM ( Sales[order] ),
        FILTER (
            ALL ( Sales ),
            Sales[Pre Product] = SELECTEDVALUE ( Sales[Pre Product] )
                && [——final] = SELECTEDVALUE ( 'Table'[Final Product] )
        )
    )
VAR times_ =
    CALCULATE (
        COUNT ( Sales[Pre Product] ),
        FILTER (
            ALL ( Sales ),
            Sales[Pre Product] = SELECTEDVALUE ( Sales[Pre Product] )
                && [——final] = SELECTEDVALUE ( 'Table'[Final Product] )
        )
    )
VAR _if =
    MAX ( 'Table'[Column] )
VAR result_times =
    IF ( times_ < _if, _if, times_ )
RETURN
    DIVIDE ( s_all, result_times )

If I have misunderstood your meaning, please provide your desired output with more details.

Best Regards

Community Support Team _ Polly

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

[johnt75]

You could create a measure as

Avg per order =
var totalUsed = CALCULATE(SUM('Sales'[Amount used]) )
var totalOrders = CALCULATE( COUNTROWS('Orders') )
return DIVIDE( totalUsed, totalOrders )

[Sporty583]

Thanks for the reply  @johnt75 but unfortunately I get the same results as with all my other tries:

The measure calculates for Pre Product D an avg of 100 whereas i need it to be 50 (see screenshot)

What else would be possible to try?

[johnt75 · Accepted solution]

try changing the totalOrders line to

var totalOrders = CALCULATE( COUNTROWS('Orders'), REMOVEFILTERS('Sales'[Pre product]) )