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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Kishore_SR
Frequent Visitor

Need help with DAX, to make measures as additive

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
  • Kishore_SR_0-1667276189373.png
  • 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
  • Kishore_SR_3-1667278437956.png
  • 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])
  • Kishore_SR_4-1667278496910.png
  • 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]))
  • Kishore_SR_2-1667278357720.png
  • 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]
  • Kishore_SR_6-1667278597896.png
  • 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 thisKishore_SR_0-1667280218966.png
  • 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])
  • Kishore_SR_1-1667280262311.png
  • 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])))
  • ResultKishore_SR_2-1667280295703.png
  • 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
  • Kishore_SR_3-1667280321395.png
  • 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)))
  • Kishore_SR_4-1667280357060.png
  • But the Expected result should be,
  • Kishore_SR_5-1667280379094.png

 

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..!!

 

 

2 REPLIES 2
ImkeF
Super User
Super User

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

ImkeF
Super User
Super User

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]))
)

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors