Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers!
Enter the sweepstakes now!Prepping for a Fabric certification exam? Join us for a live prep session with exam experts to learn how to pass the exam. Register now.
Hello all!
I'm having a problem with a measure. Total QTY is ok, I want it to show the total purchased regardless the filter context.
The problem is that I try to get the average price this way:
As you can see in the table, the measure it's not dividing by the total quantity (1028), instead it's doing it by the normal quantity.
Can anyone help me with this?
Solved! Go to Solution.
hello thank you for providing details,
kidly change the first measure i provided to :
total qtty = CALCULATE(SUM(HelpBi[Purchase Qtty]),ALL(HelpBi),HelpBi[ID]=MAX(HelpBi[ID]))
i added some sample to show you how it work:
please tell me if it works if it does please accept as solution to help other users
hello @AndresOHV ,
please try the below im not sure if i understood you correctly but pleaae try:
create 2 measures first one is:
Total QTY = CALCULATE(SUM(HelpBi[Purchase Qtty]),ALL(HelpBi))
the second one is
Average Price = SUMX(HelpBi,DIVIDE((HelpBi[Purchase Qtty]*HelpBi[Price]),[Total QTY],0))
Hi @Anonymous !
I'm copying the table below with an extra column (productId).
The problem of using ALL is that it removes the product filter and I need the measure to calculate the average price for the selected product so using ALL is not working.
Date | Product Id | Purchase qty | Price | Total QTY | Average Price |
30/12/2021 00:00 | 149 | 276 | 179.7 | 1028 | 180 |
31/12/2021 00:00 | 149 | 471 | 177.57 | 1028 | 178 |
31/12/2021 00:00 | 149 | 281 | 177.57 | 1028 | 178 |
4/1/2022 00:00 | 149 | 0 | 179.7 | 1028 | 180 |
7/1/2022 00:00 | 149 | 557 | 172.17 | 1028 | 172 |
7/1/2022 00:00 | 149 | -557 | 172.17 | 1028 | 172 |
12/1/2022 00:00 | 149 | -557 | 0 | 1028 | 0 |
12/1/2022 00:00 | 149 | 557 | 0 | 1028 | 0 |
hello thank you for providing details,
kidly change the first measure i provided to :
total qtty = CALCULATE(SUM(HelpBi[Purchase Qtty]),ALL(HelpBi),HelpBi[ID]=MAX(HelpBi[ID]))
i added some sample to show you how it work:
please tell me if it works if it does please accept as solution to help other users
Thank you so much @Anonymous ! This was exactly what I was needing! 😀
@AndresOHV Perhaps try CALCULATE([Total Qty], ALL('Table')
Hi Greg! Thank you for your answer.
That's not working, I think because I need to keep the filter of a specific product, using ALL removes that filter.
Check out the April 2025 Power BI update to learn about new features.
Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
User | Count |
---|---|
16 | |
14 | |
13 | |
12 | |
10 |
User | Count |
---|---|
11 | |
10 | |
6 | |
6 | |
6 |