The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.

**Save €200 with code MSCUST on top of early bird pricing!**

- Power BI forums
- Updates
- News & Announcements
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Custom Visuals Development Discussion
- Health and Life Sciences
- Power BI Spanish forums
- Translated Spanish Desktop
- Training and Consulting
- Instructor Led Training
- Dashboard in a Day for Women, by Women
- Galleries
- Community Connections & How-To Videos
- COVID-19 Data Stories Gallery
- Themes Gallery
- Data Stories Gallery
- R Script Showcase
- Webinars and Video Gallery
- Quick Measures Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Events
- Ideas
- Custom Visuals Ideas
- Issues
- Issues
- Events
- Upcoming Events
- Community Blog
- Power BI Community Blog
- Custom Visuals Community Blog
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

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

- Power BI forums
- Forums
- Get Help with Power BI
- Desktop
- Need help with DAX, to make measures as additive

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Need help with DAX, to make measures as additive

10-31-2022
10:26 PM

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

2 REPLIES 2

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

11-02-2022
02:19 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

11-01-2022
10:34 PM

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

Announcements

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

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

Featured Topics

Top Solution Authors

User | Count |
---|---|

121 | |

112 | |

72 | |

42 | |

39 |

Top Kudoed Authors

User | Count |
---|---|

205 | |

101 | |

73 | |

73 | |

66 |