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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Community Champion
Community Champion

@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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.