Need help with DAX, to make measures as additive

[Kishore_SR]

Hello All,

I am doing Statistical Analysis - ChiSquare Test in PowerBI using DAX,

My main goal is to only dispaly the Final result of ChiSqaure Test which is P-Value, which involves series of calculations. The issue i am facing is I can't able to add 3 measures (which is non additive), so I need to make these measures as additive in nature.

This is the Data Model, 

• JAR Attributes is the main table
• 
• JAR_Year 1 and JAR_Year 2 tables are created for the comparison of two Years
• Function of JAR_Year 1: JAR_Year 1 = DISTINCT (VALUES ('JAR Attributes'[YEAR]))
• Function of JAR_Year 2: JAR_Year 2 = 'JAR_Year 1' (Note: I have made the relationship b/w JAR Attributes and JAR_Year 2 table as Inactive, because making them as active the filtering and comparing of two years will not be possible)
• Main Purpose of this test is to calculate the P value, Test Results for YOY comparison
• For the calculation of P value, we need to calculate Observed, Expected and Chi-square values
• We have three columns Too Less, Just About Right, Too Strong in the JAR Attributes table. We need to calculate Observed, Expected, Chi-square and P values from these 3 columns

Now I am gonna explain the calculations involved in this test, 

• Too Less Observed - (Measure name - #Too Less Obs_M = IF(SELECTEDVALUE('JAR_Year 1'[YEAR]),CALCULATE(SUM('JAR Attributes'[Too Less]),'JAR Attributes'[PRODUCT_1ST]="M")) + IF(SELECTEDVALUE('JAR_Year 2'[YEAR]),CALCULATE(SUM('JAR Attributes'[Too Less]),'JAR Attributes'[PRODUCT_1ST]="M",ALL('JAR_Year 1'[YEAR]),USERELATIONSHIP('JAR Attributes'[YEAR],'JAR_Year 2'[YEAR])))
• Just About Right Observed - (Measure name - #JAR Obs_M = IF(SELECTEDVALUE('JAR_Year 1'[YEAR]),CALCULATE(SUM('JAR Attributes'[Just About Right]),'JAR Attributes'[PRODUCT_1ST]="M")) + IF(SELECTEDVALUE('JAR_Year 2'[YEAR]),CALCULATE(SUM('JAR Attributes'[Just About Right]),'JAR Attributes'[PRODUCT_1ST]="M",ALL('JAR_Year 1'[YEAR]),USERELATIONSHIP('JAR Attributes'[YEAR],'JAR_Year 2'[YEAR])))
• Too Strong Observed - (Measure name - #Too Strong Obs_M = IF(SELECTEDVALUE('JAR_Year 1'[YEAR]),CALCULATE(SUM('JAR Attributes'[Too Strong]),'JAR Attributes'[PRODUCT_1ST]="M")) + IF(SELECTEDVALUE('JAR_Year 2'[YEAR]),CALCULATE(SUM('JAR Attributes'[Too Strong]),'JAR Attributes'[PRODUCT_1ST]="M",ALL('JAR_Year 1'[YEAR]),USERELATIONSHIP('JAR Attributes'[YEAR],'JAR_Year 2'[YEAR])))
• These 3 measures are for the calculation of Observed values
• 
• Then have to calculate the Expected values, Formula for Expected - (Expected = Column total * Row total/ Grand total)
• Row Total - (Measure name - #Row Total_M = [#Too Less Obs_M] + [#JAR Obs_M] + [#Too Strong Obs_M]
• Too Less Column Total - (Measure name - #Less Column Total_M = IF([#JAR Obs_M] >0,CALCULATE([#Too Less Obs_M],ALLSELECTED('JAR Attributes'[YEAR])))
• JAR Column Total - (Measure name - #JAR Column Total_M = IF([#JAR Obs_M] >0,CALCULATE([#JAR Obs_M],ALLSELECTED('JAR Attributes'[YEAR])))
• Too Strong Column Total - (Measure name - #Strong Column Total_M = IF([#JAR Obs_M] >0,CALCULATE([#Too Strong Obs_M],ALLSELECTED('JAR Attributes'[YEAR])))
• Grand Total - (Measure name - #Grand Total_M = CALCULATE ([#Less Column Total_M] + [#JAR Column Total_M] + [#Strong Column Total_M])
• 
• Now using Row Total, Column Total and Grand Total, we calculate Expected values
• Too Less Expected - (Measure name - #Too Less Expect_M = CALCULATE(DIVIDE([#Less Column Total_M] * [#Row Total_M], [#Grand Total_M]))
• JAR Expected - (Measure name - #JAR Expect_M = CALCULATE(DIVIDE([#JAR Column Total_M] * [#Row Total_M], [#Grand Total_M]))
• Too Strong Expected - Measure name - #Too Strong Expect_M = CALCULATE(DIVIDE([#Strong Column Total_M] * [#Row Total_M], [#Grand Total_M]))
• 
• Now using Observed and Expected values, we calculate the Chi Square values
• Formula for Chi Square - ChiSqu = ∑ (Observed – Expected) ^2/Expected
• Too Less ChiSqu - Measure name - #Too Less CS_M = CALCULATE(([#Too Less Count_M] - [#Too Less Expect_M])^2/ [#Too Less Expect_M])
• JAR ChiSqu - Measure name - #Just About Right CS_M = CALCULATE(([#JAR Count_M] - [#JAR Expect_M])^2/ [#JAR Expect_M])
• Too Strong ChiSqu - Measure name - #Too Strong CS_M = CALCULATE(([#Too Strong Count_M] - [#Too Strong Expect_M])^2/ [#Too Strong Expect_M]
• 
• Now I need to add all the 6 values in those 3 measures (Total Chi Square = 0.154 + 0.152 + 0.266 + 0.264 + 0.884 + 0.875 = 2.595) and have it like this
• What i did is, first I have calculated each row total
• #Total CS_M = CALCULATE([#Too Less CS_M]+[#Just About Right CS_M]+[#Too Strong CS_M])
• 
• Now I tried to calculate the total sum of #Total CS_M, which is 1.304 + 1.291 = 2.595
• #ChiSq_M = IF(SELECTEDVALUE('JAR_Year 1'[YEAR]),CALCULATE([#Total CS_M],ALLSELECTED('JAR_Year 2'[YEAR]))) + IF(SELECTEDVALUE('JAR_Year 2'[YEAR]),CALCULATE([#Total CS_M],ALLSELECTED('JAR_Year 1'[YEAR])))
• Result
• Note: I tried the above function, instead of Sum of 1.304 + 1.291 = 2.595 it is doing 1.304 + 1.304 = 2.608 and 1.291 + 1.291 = 2.583
• But my Expected result should be
• 
• Right now, because of two different Chi Square values, the P values also gives two different values… but the result should give only one Chi Square, P values
• P value_M = IF([#JAR Obs_M]>0,CALCULATE(CHISQ.DIST.RT([#ChiSq_M],2)))
• 
• But the Expected result should be,
• 

Hope I cleared out all the information needed, If anyone find this difficult to understand please let me know

Really need help in solving this issue, dealing with this for a long time now

Any help wolud be appreciated, Thanks in Advance..!!

[ImkeF]

Hi @Kishore_SR ,
thanks for the file.
My suggestions didn't work, but due to the complexity of your calculations, I cannot find the source for it in a reasonable time.
So hopefully someone else will pick this up.

[ImkeF]

Hi @Kishore_SR ,
for this kind of calculation in DAX, you need to understand the concepts of row- and filtercontext:
Row context in DAX - SQLBI
And although you made a very good description of your problem, I find it a bit hard to follow without sample data. So if the following options don't work, please provide some sample data that reconciles with the values you've given.
Options:

CALCULATE(
     [#Too Less CS_M]+[#Just About Right CS_M]+[#Too Strong CS_M], REMOVEFILTERS(TableNameFromYearInVisual[Year]))

or:

#Total CS_M = 
SUMX(
ALL(TableNameFromYearInVisual[Year]),
CALCULATE(
     [#Too Less CS_M]+[#Just About Right CS_M]+[#Too Strong CS_M], REMOVEFILTERS(TableNameFromYearInVisual[Year]))
)