Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hi,
I am trying to calculate the total quantity by Quality from a table called "Data", but filtering on the highest value in Period (in this case, 201906). The purpose is to calculate the variance for each quality (highest period/lowest period).
In other words, I would like to get for each product Quantity [Period 201906] / Quantity [Period 201902]
I have tried different formulas but I'm struggling with the filter context.
Can anyone help me?
Thanks!
Solved! Go to Solution.
How would you proceed, then, with Query M?
Thanks @Anonymous and @Greg for your solutions. My description of the issue is probably not accurate enough. What I am looking for is not retrieving the minimum quantity for each quantity. I want to retrieve the quantity for the Maximum value in "Period".
So for Quality "A", I want to retrieve the quantity where the value "Period" is the highest, so 10, not 13.
Then I would like to do the same for the minimum Period (so retrieve Quantity 5 for Quality A in @Anonymous 's example).
In the end, I would divide the Quantity for the highest Period by the Quantity for the lowest Period, for each Quality. That would give me an index, which is the scope of all the above.
I thought that DAX was the right language to solve this question but I didn't figure out how, despite may trials.
Thanks again for your help!
OK. So you need another table (created in PQ) that for each quality will store the quantities for the latest and oldest period.
I'll post a link to a file shortly.
Best
Dare
Yes, I think that should be a way to go. 🙂
Create a DAX formula that goes something like this:
Measure IsHighest = VAR __table = SUMMARIZE(ALLSELECTED('Table'),[Quality], [Period],"__Quantity",SUM([Quantity])) VAR __max = MAXX(__table,[__Quantity]) VAR __period = MAXX(FILTER(__table,[__Quantity] = __max),[Period]) RETURN IF(MAX([Period]) = __period,1,0)
Then just fiilter on this being 1
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
11 | |
9 | |
8 | |
8 | |
8 |
User | Count |
---|---|
13 | |
12 | |
11 | |
10 | |
9 |