- 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
- Power Platform Integration - Better Together!
- Power Platform Integrations
- Power Platform and Dynamics 365 Integrations
- 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

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

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

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

Featured Topics

Top Solution Authors

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

144 | |

126 | |

112 | |

108 | |

66 |

Top Kudoed Authors

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

152 | |

147 | |

130 | |

84 | |

68 |