Calculate average price based on certain condition

Nun 

Create a measure to calculate the total PC:

TotalPC = SUM('Table'[PZ])

Create a measure to calculate 10% of the total PC:

ThresholdPC = [TotalPC] * 0.1

Create a calculated column to rank the rows by EUR/pz:

Rank = RANKX('Table', 'Table'[EUR/pz], , DESC, DENSE)

Create a measure to calculate the cumulative sum of PZ:

DAX
CumulativePZ =
CALCULATE(
SUM('Table'[PZ]),
FILTER(
ALL('Table'),
'Table'[Rank] <= MAX('Table'[Rank])
)
)

Create a measure to filter the rows within the 10% threshold:

DAX
TopPricesPZ =
CALCULATE(
SUM('Table'[PZ]),
FILTER(
'Table',
[CumulativePZ] <= [ThresholdPC]
)
)

Create a measure to calculate the sum of EUR for the selected rows:

DAX
TopPricesEUR =
CALCULATE(
SUM('Table'[EUR/pz] * 'Table'[PZ]),
FILTER(
'Table',
[CumulativePZ] <= [ThresholdPC]
)
)

Finally, create a measure to calculate the average price:

DAX
AveragePrice = [TopPricesEUR] / [TopPricesPZ]

Thanks for sharing this here!!!

Hello,
thanks a lot! Here a table consider that PZ is sum of Pz (measure) and EUR/PZ is a measure too.I should get 

Hi Nun ,

Sorry for the inconvenience and delay response.

Assume that your table name is SalesData:

TotalPZ = CALCULATE(SUM(SalesData[PZ]))

Try to Create a Ranked Table (sorted by EUR/PZ ascending)

RankedTable =
ADDCOLUMNS (
    FILTER (
        ADDCOLUMNS (
            SalesData,
            "EURPerPZ", SalesData[EUR] / SalesData[PZ]
        ),
        NOT ISBLANK(SalesData[PZ]) && SalesData[PZ] > 0
    ),
    "Rank", RANKX (ALL (SalesData), SalesData[EUR] / SalesData[PZ], , ASC, DENSE)
)

You can also pre-create EURPerPZ as a calculated column.)

Next, please use the below DAX to calculate Cumulative PZ and Filter for Top 10%

Top10PctTable =
VAR TotalPZ = CALCULATE(SUM(SalesData[PZ]))
VAR PZTarget = TotalPZ * 0.10
VAR SortedTable =
    ADDCOLUMNS (
        FILTER (
            SalesData,
            NOT ISBLANK(SalesData[PZ]) && SalesData[PZ] > 0
        ),
        "EURPerPZ", SalesData[EUR] / SalesData[PZ]
    )
VAR SortedByPrice =
    ADDCOLUMNS (
        ADDCOLUMNS (
            SortedTable,
           "CumulativePZ",
            SUMX (
                FILTER (
                   SortedTable,
                    (SalesData[EUR] / SalesData[PZ]) <= EARLIER(SalesData[EUR] / SalesData[PZ])
                ),
                SalesData[PZ]
            )
        ),
       "IncludeRow",
        IF (
            SUMX (
                FILTER (
                   SortedTable,
                    (SalesData[EUR] / SalesData[PZ]) <= EARLIER(SalesData[EUR] / SalesData[PZ])
                ),
                SalesData[PZ]
            ) <= PZTarget,
            1,
            0
        )
    )
RETURN
    FILTER (SortedByPrice, [IncludeRow] = 1)

As a final step, create a below measure to calculate Weighted Avg Price for Top 10% PZ:

Top10Pct_EURPerPZ =
VAR TotalPZ = CALCULATE(SUM(SalesData[PZ]))
VAR PZTarget = TotalPZ * 0.10
VAR SortedTable =
    ADDCOLUMNS (
        FILTER (
            SalesData,
            NOT ISBLANK(SalesData[PZ]) && SalesData[PZ] > 0
        ),
        "EURPerPZ", SalesData[EUR] / SalesData[PZ]
    )
VAR Ordered =
    ADDCOLUMNS (
        SortedTable,
        "SortOrder", RANKX(SortedTable, [EURPerPZ], , ASC)
    )
VAR Running =
    ADDCOLUMNS (
        Ordered,
        "RunningPZ",
        SUMX (
            FILTER (
                Ordered,
                [SortOrder] <= EARLIER([SortOrder])
            ),
            SalesData[PZ]
        )
    )
VAR Trimmed =
    FILTER (
        Running,
        [RunningPZ] <= PZTarget
    )
VAR TotalEUR =
    SUMX (
        Trimmed,
        SalesData[EUR]
    )
VAR TotalTrimmedPZ =
    SUMX (
        Trimmed,
        SalesData[PZ]
    )
RETURN
    DIVIDE(TotalEUR, TotalTrimmedPZ)

I hope this will resolve your issue, if you need any further assistance, feel free to reach out.

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

Thankyou.

Hi,

unfortunately, I didn't solve the issue. We need to consider that EUR/PZ is a measure and not an existing column. We have the column EUR, PZ but not EUR/PZ. We have the same agent that sells to different delivery customer, the same product (or different) in the same quarter with different EUR and different quantity PZ. Creating the rank, ofcourse there several rows with the same rank. Based on your model, I created a column EUR/PZ, then a column Rank, using the column EUR/PZ, that a measure TopEUR, 10% and the TopAvg, but the result is so far to be correct.

Please take a look of this example for a selected product in Q2 2024 PZ and EUR are measure. The total PZ is 1202 for Q2, the calculated 10% is 120,2, 9761,07 is PZ*EUR/PZ. As end result what we need to get is the top price 345,49. Applying your formulas, I get for some reason, 363. Thanks!

Hi Nun ,

Can you please confirm whether you have resolved issue. If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. This will be helpful for other community members who have similar problems to solve it faster. 

If we don’t hear back, we’ll go ahead and close this thread.Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread. We’ll be happy to help.

Thank you.

No, I didn't resolve the issue, in a post you were mentioning to create a table rank ( I remember correctly), but I cannot find that post.Thanks

Hi Nun ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

Hi Nun ,

Since we have tried all possible solutions, I recommend you to raise a partner support request so they can assist you further with this issue.

Please use the link below to submit your request: Re: Calculate average price based on certain condi... - Page 2 - Microsoft Fabric Community

Thank you.

Hi.

Thanks so much for all. I will continue to find a solution by myself. I have contacted a partner support but the costs are not compatible with the request.

Thank you