Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a table that has an annual amount for users that is not tied to any dates:
In a different table I have this setup:
(these 2 tables are already related through a date table). So what I need to figure out is how to get a cumulative total based on the annual amount. So for instance, we are in February. A person should have hit a total of 12% of their annual quota (6.5% from January + 5.5% from February). So for the first person with a quota of $180,000.56, they should right now be at $21,600.07. I have a formula that does return a cumulative number, however it sums it up for the entire table instead of each individual person:
Which results in:
If I click on a specific person's name, the correct value returns, but unfiltered everybody has the same $ value. How can I fix this? The idea is to have it dynamic so we can see in the future how much $ a person is expected to have by a certain date.
Other formulas involved in the above:
Solved! Go to Solution.
Hi @Anonymous ,
If i understand you correctly, please refer to the measure below.
Measure = SELECTEDVALUE(quota[quota ])*CALCULATE(SUM('quota%'[quota%]),FILTER(ALL('quota%'),'quota%'[month]<=SELECTEDVALUE('quota%'[month])))
Raw data:
Result would be shown as below.
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous , above solution/s has worked out for you.
Hi @Anonymous ,
If i understand you correctly, please refer to the measure below.
Measure = SELECTEDVALUE(quota[quota ])*CALCULATE(SUM('quota%'[quota%]),FILTER(ALL('quota%'),'quota%'[month]<=SELECTEDVALUE('quota%'[month])))
Raw data:
Result would be shown as below.
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
There is a running total quick measure in the Desktop that might work. Otherwise, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |