Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello Community -
So our ERP system kicks out a bunch of snapshot data and KPIs (we call them critical numbers). Using the filter pane, I can select the fields and filter to get to a sum value, such as in the example below.
However, what I really need is to be able to sum these KPIs (critical number) to acheive the same thing using a Measure. I've tried using Calculate and Filter to achieve this, but I can't make it happen. And ideally what I need is the measure to be based on TODAY. So that each day, it dynamically updates.
Also, I currently have this Critical Number set to a "whole number". It really should probably be a text field, but if I change it, I lose the ability to put it into a visual (gives me an error message).
That's the least of my concerns as the main thing I really need is how to create a measure that sums up each of these items in the "description" field below, based on an "as of date" of today.
Solved! Go to Solution.
@Greg_Decklerwith your help I was able to get to a solution that worked. Thanks so much!
Here is what I used:
Difficult to be exact without source data. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
But hopefully this is close enough to get you where you need to be.
Measure =
VAR __Today = TODAY()
VAR __Table =
GROUPBY(
FILTER(
'Table',
[Critical Number] = 4292 &&
[As Of Date] = __Today
),
[Description],
"__Actual Value",SUMX(CURRENTGROUP(),[Actual Value])
)
RETURN
SUMX(__Table,[__Actual Value])
Thanks for the quick reply. I tried your formula but I think what it is missing is the descriptions of the respective codes (that may not have been obvious from my initial post). I ended up with this, which works well, other than it extends out past months where there is no data (shows the same value for the remaining months in a bar chart for example). Not a huge deal and I think I can figure that out.
Not sure if there is a way to adapt your formula as I know there are always several ways to skin the cat in PBi.
@Greg_Decklerwith your help I was able to get to a solution that worked. Thanks so much!
Here is what I used:
Well, you could always turn that into a VAR and then have a quick check in your RETURN to return BLANK perhaps.
User | Count |
---|---|
93 | |
85 | |
78 | |
68 | |
62 |
User | Count |
---|---|
113 | |
99 | |
96 | |
64 | |
58 |