Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
Below is a simple exercise I made in order to try to understand/learn the concept of evaluation context.
The table shows projects, department, month and budgetrevision and budget. BudgetMax is a measure I've created in order to get the latest budgetrevision.
In the clustered column chart I've highlighted the colum for 201807, which then filters the table. However, what I really want BudgetMax to do is show the latest Budget amount (based on revision number), for each project. As you can see, the table shows there are 2 unique project numbers in 201807, and so I want the BudgetMax measure to show 20 000 + 30 000 = 50 000 (both in the table and in the chart).
As you can see from the BudgetMax measure, I'm trying to combine sumx & summarize to solve the problem, without really understanding how they work.
Any help would be greatly appreciated.
TL-DR: Want measure to show latest budget per project, per month
can you screeshot your model?
there is no model currently, only this table. This is just to get an understanding of the basics.
so, when you choose your 201807 in the graph, you're filtering out the three rows 201807. Your filter context (albeit simple as you have only one table) is that, those three rows.
Now if you want to show only a single budgetrevision( as an example) you'll have to put another slicer with the budgetrevision column and when you select "4" your filter context will be the union of 201807 and 4.
If you put a table with MonthKey in columns and Project in rows, each cell of your table will have a specific filter context, made by the union of those values of that column.
So you then create a measure
Budget=SUMX(table;table[budget])
When you put that measure in the VALUES field of your table, it will calculate the values for each cell, considering the filter context of that cell, so in fact showing the data as you want.
thanks for the reply, but I obviously can't use budgetrevision in a slicer, since I don't know the number of the last revision for each and every project (in a more realistic example). That's why I'm using Max, in order to return the highest value of revision.
I realize that my measures the evaluation context changes based on the visuals I use, but I need to manipulate my measures to show the context I want, even if the visual context might be different.
Example; If I just wrote a measure that returned [budget] based on max[budgetrevision] and showed this in a month by month chart, it would only show 1 budget for 1 project - I need it to show the "latest" budget for all the projects in this particular month - and this is where I need some help
Create a measure
Thank you for the reply. This helped me along the right path I feel, even though this doens't look entirely correct either. In retrospect, I think my description of the problem is somewhat lacking, so I've decided to make a new thread with more detail and expand the problem abit.
Thanks again.
Actually, you don't really need to create the measure. You set up the graph with "SUM" as aggregation . see here:
https://drive.google.com/file/d/1vjstOO638dLNFusq5HvGM2y7JuFppxdB/view?usp=sharing
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |