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.
Dear colleagueas,
I'm trying to make this measure work for the past couple of days, but it seems over my head. I tried many things explianed elsewhere about accumulated measures, but it seems my case is somewhat special. I think I got pretty near the solution, but can't get it.
I need a MEASURE to calculate the accumulated total of another measure, as in the tabel below:
My current formula for "Total Accumulated" measure is:
=
CALCULATE (
MAXX (
fDCAs;
COUNTROWS ( FILTER ( fDCAs; fDCAs[Rev] <= EARLIER ( fDCAs[Rev] ) ) )
);
USERELATIONSHIP ( dCalendar[Date]; fDCAs[Data Aprovação DCA] )
)
NOTE 1: I need a MEASURE, and not a CALCULATED COLUMN. This adds a bit of complexity when using EARLIER, since it needs a previous row context.
NOTE 2: The table has filters on the FILTER field and Slicers with DATES.
NOTE 3: For the TOTAL APPROVAL measure I have to use an inactive relationship between the CALENDAR table and the data table.
NOTE 4: I'm actually using PowerPivot.
The file XLSX file is available on the following link.
Thank you very much for the help,
Solved! Go to Solution.
@anmattos Forgot the ALL
Total Approval =
VAR __Rev = MAX('fDCA'[Rev])
VAR __Table =
ADDCOLUMNS(
DISTINCT(ALL('fDCA'[Rev])),
"__TotalApprovals",[Total Approvals]
)
RETURN
SUMX(FILTER(__Table,[Rev]<=__Rev),[__TotalApprovals])
@anmattos Try:
Total Approval =
VAR __Rev = MAX('fDCA'[Rev])
VAR __Table =
ADDCOLUMNS(
DISTINCT('fDCA'[Rev]),
"__TotalApprovals",[Total Approvals]
)
RETURN
SUMX(FILTER(__Table,[Rev]<=__Rev),[__TotalApprovals])
Hello @Greg_Deckler ,
Thank you very much for the proposed solution. Unfortunately it returned the same results as my current formula.
Best regards,
@anmattos Forgot the ALL
Total Approval =
VAR __Rev = MAX('fDCA'[Rev])
VAR __Table =
ADDCOLUMNS(
DISTINCT(ALL('fDCA'[Rev])),
"__TotalApprovals",[Total Approvals]
)
RETURN
SUMX(FILTER(__Table,[Rev]<=__Rev),[__TotalApprovals])
Wow!!!
Thank you @Greg_Deckler ! It worked! Now I´ll need more 3 days to understand it, but the problem is solved.
Thank you very much for your time!
@anmattos Here's a walk-through:
Total Approval =
VAR __Rev = MAX('fDCA'[Rev]) // Get the current value of Rev column in the current row
VAR __Table =
ADDCOLUMNS(
DISTINCT('fDCA'[Rev]), // Since we are in a visual where there is a single value of Rev in context, override this and get all the values of the distinct values of Rev in a single column table.
"__TotalApprovals",[Total Approvals] // Add the measure to this single column table and the values returned are for the individual Rev values in the current virtual table row.
)
RETURN
SUMX(FILTER(__Table,[Rev]<=__Rev),[__TotalApprovals]) // SUM up the measure in our virtual table but filter the table such that only "previous" rows are summed.
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 |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |