Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors