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.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

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.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors