Calculate average price based on certain condition

[Nun]

I have this table, 

Agent	PZ	EUR/pz
A	100	280
	50	275
C	220	260
D	130	258
E	300	255
F	500	253
G	80	252
H	20	240
I	300	239
J	700	238
K	750	235
L	50	225
M	60	220
N	40	215
O	200	210

PZ are calculated based on a formula sum (PC), EUR/pz is a formula too sum(eur)/sum(PC)
now I need to calculate the average price of highest prices falling within 10% of total PC. An example: in the table the total PC is 3500, the 10% is 350 so A,B,C are the top prices which have the pz amount within 10%, so 

we EUR 93750 (below table) PC 350 TOP EUR *PC 268 (93750/350),

PC x EUR/PC

28000

13750

52000

[Nun]

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!

[v-tsaipranay]

Hi @Nun , 

Thank you for your continued efforts in testing the approaches we’ve provided. As some time has passed and multiple solutions have been attempted without achieving the desired outcome, we kindly suggest raising a support ticket with the Microsoft product team. They will be able to investigate the matter further and provide more in-depth assistance to help resolve the issue.

You can raise a support ticket using the following link: Submit a product support request

Additionally, if you do find a resolution through the support channel, we would sincerely appreciate it if you could share your findings with the community. Your insights may help other members facing similar challenges.

Thank you for your patience and understanding. Please continue to engage with the Fabric Community for any further questions or support needs.

[v-tsaipranay]

Hi @Nun ,

We are following up once again regarding your query. Could you please confirm if the issue has been resolved through the support ticket?

If the issue has been resolved, we kindly request you to share the resolution or key insights here to help others in the community. 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 for your understanding and participation.

[Nun]

Hi,

unfortunately I cannot open a ticket. I have PRO license, but I guess in my organization we cannot open a ticket

I try to figure out how the get the cumulative, considering that I don't have as a column EUR/PZ but I have EUR and PZ column, and different Prices (EUR colum) for different quantities. Because the same agent can buy the same product, during a quarter, different times, with different prices. Ofcourse differnt agents can buy the same product with same price (EUR) and different PZ. SUM PZ is a measure EUR/PZ is a measure in our table. 

[v-tsaipranay]

Hi  @Nun, 

If you have a Pro license you can open a Pro ticket at https://admin.powerplatform.microsoft.com/newsupportticket/powerbi
Otherwise you can raise an issue a...https://community.fabric.microsoft.com/t5/Issues/idb-p/Issues .

Pro ticket response time varies depending on your company's support contract.  First level support is a Microsoft partner who will contact you according to your stated preferences (phone/email) and will arrange for the initial triage call.

Thank you.

[v-tsaipranay]

Hi @Nun ,

Could you please confirm if the issue has been resolved after raising a support case using pro license? If a solution has been found, it would be greatly appreciated if you could share your insights with the community. This would be helpful for other members who may encounter similar issues.

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 for your understanding and participation.

[Nun]

Hello,
I opened a support request, I did not reply because the problem is being analysed to propose a solution. I will keep you updated

[Nun]

Hello, they didn't support me. They cannot give any supports to build a dax query. They suggested to contact a partner. 

[v-tsaipranay]

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.

[Nun]

HI @v-tsaipranay , thanks so much for supporting, I really appreciate it!
I created the Ranked Table, then I tryed to create the table (Top10Pcttbale)  to calculate Cumulative PZ and Filter for Top 10%, but I can't because I get this error:"The first argument of EARLIER/EARLIEST is not a valid column reference in the earlier row context.". Then I created the measure to calculate Weighted Avg Price for Top 10% PZ. But I do not understand the relationship between the three functions. My final aim is to determine the average price of the prices that have a quantity of 10% of the total, for a given period, for a selected product. In the attached tables, the top price 273.28 is the average price of product X for the first quarter, for the total of products that are equal to 10% of the overall total

[v-tsaipranay]

Hi @Nun ,

To calculate the weighted average EUR/pz for the top 10% of total PZ (prioritizing by highest price). I implemented a DAX-based approach and verified the result.

For your reference, I’ve attached the .pbix file so you can review the implementation and DAX measures used.

Please feel free to open the file and explore the visuals and calculations.

Thank you.

[v-tsaipranay]

Hi @Nun ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[Nun]

Hi, 

thanks so much for all the support, I tested the file attached, but the result is not what it should be. Please find enclosed how should be calculated the avg based on the top price with a PZ amount excatly macthing with the 10% of total PZ.

Considering that the 10% is 350, I will take all the agents with till the total is equal to the 10% (A-B-C). now the total doesn't match because it is 370, so from the last will be used 200 (370-350 is 20, so from C=220, to match to 350 we reduct it by 20). Then we need to moltiply the PZ for the EUR/pz, but for agent C will be 200*8,08. The total will be diveded by the 10%(350). The result is 8.767. In the power BI file the Top10Pct_Avg_Price is 9.29. Hope I was clear. Thanks!

[v-tsaipranay]

Hi @Nun ,

I’ve reviewed and updated the file. It appears the difference in results was due to a mismatch between your dataset and the one I initially referenced.

The calculations and measures you used were correct—no issues there. The logic was sound; only the data source differed.

I am also including the screenshot for your reference:

Please feel free to open the file and explore the visuals and calculations.

Thank you.

[v-tsaipranay]

Hi @Nun ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[Nun]

Hi, thanks for supporting.

I am testing it, because our scenario is a little different. First of all Eur/PZ is a measure, we have Eur for each row. It means we have the same agent that can buy the same product for several times in the same quarter, with different prize (Eur) and different quantity (PZ). 

[v-tsaipranay]

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.

[Nun]

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 

Customer	Delivery customer	PZ	EUR/pz
A	AA	4,631	296,6076441
A	BB	4,104	291,4205653
A	CC	23,39	286,9392903
A	CC	4,961	282,9953638
A	…	5,623	282,4399787
A		10,254	278,0856251
A		18,298	277,7456553
A		15,877	277,1902752
A		5,623	276,6548106
A		15,388	276,4121393
A		6,899	276,2095956
A		10,915	274,5112231
A		14,16	273,9413842
A		11,867	271,0878908
A		9,922	268,8218101
A		17,522	268,3449378
B		26,46	266,9780801
C		40,684	266,795792
A		5,292	266,7611489
A		9,592	264,8415346
A		18,524	264,3462535
A		9,592	264,2003753
A		21,5	263,992093
D		172,329	263,9917251
A		21,791	262,3950255
A		9,262	259,0671561
A		5,623	257,5866975
A		74,689	257,2599713
A		9,922	255,1330377
A		42,338	255
A		491,009	254,8750023
….		16,207	252,7210465
		19,912	252,5271193
		228,743	249,5453413
		50,548	248,844069
		90,31	245,6905105
		134,544	245,2783476
		9,016	243,5337178
		259,57	242,9669453
		16,207	241,8905411
		18,705	240,7399091
		127,008	238,1796422
		37,008	237,6513186
		58,579	237,2280169
		43,332	236,7659005
		54,874	233,3671684
		5,954	232,8249916
		41,636	232,3311557
		40,683	232,3184131
		4,631	230,969553
		23,816	230,5710447
		43,324	229,251685
		37,231	227,5069163
		55,441	226,3701953
		4,874	224,3906442
		29,108	222,9359626
		93,878	222,7130957
		9,592	220,6380317
		5,954	215,6415855
		85,931	214,1419278
		54,104	173,5557445
Total		2838,761

[bhanu_gautam]

@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]

[v-tsaipranay]

Hi @Nun ,

Thank you for reaching out to the Microsoft Fabric Community Forum.

As you've described, you're aiming to compute the average unit price (EUR/pz) of the highest-priced entries, constrained to the top 10% of total PZ volume. The idea is to rank entries by EUR/pz in descending order and include rows until their cumulative PZ reaches the 10% threshold then calculate a weighted average on that subset.

The approach provided by @bhanu_gautam  captures this logic accurately using a combination of ranking, cumulative sums, and conditional filters in DAX.

As a small note, please ensure that missing or blank values (such as null PZ or EUR/pz) are excluded in your model to avoid calculation errors.

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.