cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tjd
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:

 

tjd_1-1610744902799.png

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])
RETURN
IF(HASONEVALUE('FACT-ELEMENTGRADES'[GradeValue]),[CIs],SUMX(__table,[CIs]))
 
 
m_Total 1Fil =
VAR __table = SUMMARIZE('FACT-ELEMENTGRADES', [GradeValue],"__CI",[CIs])
RETURN
IF(HASONEFILTER('FACT-ELEMENTGRADES'[GradeValue]),[CIs],SUMX(__table,[CIs]))
 
Any tips or help would be greatly appreciated.
3 REPLIES 3
themistoklis
Super User II
Super User II

@tjd 

 

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

 

CIs = SUMX (
VALUES ( 'FACT-ELEMENTGRADES'[GradeValue] ),
ABS([% Graded MI Distribution (Value)]-[% Graded MI Distribution (Value-All MenuItems)])
)

tjd
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):

tjd_0-1610748407524.png

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.

tjd
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

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors
Top Kudoed Authors