Skip to main content
Showing results for 
Search instead for 
Did you mean: 

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.

Impactful Individual
Impactful Individual

Table Measure Totals - Again

I'm trying to calculate the total difference between two grading distributions (MIs).  One group, ALL, is across all trainees, and the other, Cell/Value, is for just a selected trainee (cross-filtered).  CIs are the absolute differences between the two distributions.  For some reason, I cannot get CIs to sum.  I used the "Has One Value" technique (m_Total 1Val), but you can see that while it works, it does not return results for grade values in which the selected trainee has not received any grades.  So, I tried using "Has One Filter" (m_Total 1Fil) and got all the CI values back, but it only sums those CI values for which the trainee has a grade; it does not include the CI results for which the trainee did not receive a grade. Note, the table is further filtered to show only grade values >0:



The DAX I am using are:

% Graded MI Distribution (Value) = DIVIDE('FACT-ELEMENTGRADES'[# of MIs (U) (cell)], [Row Total (Values)],0)+0
% Graded MI Distribution (Value-All MenuItems) = CALCULATE(DIVIDE('FACT-ELEMENTGRADES'[# of MIs (U) (cell)], [Row Total (Values)],0), All('*SESSION_BRIDGE'[Session Menu Item Values]))+0
CIs = ABS([% Graded MI Distribution (Value)]-[% Graded MI Distribution (Value-All MenuItems)])
m_Total 1Val =
VAR __table = SUMMARIZE('FACT-ELEMENTGRADES', [GradeValue],"__CI",[CIs])
m_Total 1Fil =
VAR __table = SUMMARIZE('FACT-ELEMENTGRADES', [GradeValue],"__CI",[CIs])
Any tips or help would be greatly appreciated.
Community Champion
Community Champion



Can you change the CIS formula to the following one and see if it works?


CIs = SUMX (
ABS([% Graded MI Distribution (Value)]-[% Graded MI Distribution (Value-All MenuItems)])

Impactful Individual
Impactful Individual

That results in a CIs total but does not return the two rows where no grades were awarded (1 and 2):


I suspect that has to do with the fact that the individual selected (cross-filtered and has no grades of 1 or 2) is causing VALUES is returning a blank value for those two entries.

Impactful Individual
Impactful Individual

Values calculation is returning a blank for grades of 1 and 2 and not including the calculated CIs for those two in the total.  Any workarounds?

Helpful resources

Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City


Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.