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

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

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.

Top Solution Authors