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

Showing results for

- Microsoft Power BI Community
- Galleries
- Quick Measures Gallery
- Mann-Kendall Test

Highlighted
##

**Did I answer your question? Mark my post as a solution!**

Greg_Deckler

Super User

Mann-Kendall Test

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-03-2019
01:32 PM

@Route217 reached out to me in a private message and asked me about something called the Mann-Kendall Test. Apparently it is a statistics thing created by evil mathematicians. You can read about it here:

https://www.real-statistics.com/time-series-analysis/time-series-miscellaneous/mann-kendall-test/

This PBIX file implements the Mann-Kendall Test completely in measures, making it 100% dynamic. The data used in the PBIX file is the same as in the URL from real-statistics.

The most interesting measure is the one for calculating "S". Calculating "S" looks like it would involve iteration, and it basically does. As we all know, normally iteration is out-of-bounds for DAX but luckily, with some tricky nested DAX we can overcome it!

__S = VAR __table1 = ADDCOLUMNS('MK',"__S", VAR __value = [Value] VAR __table = FILTER(ALLSELECTED('MK'),[ID] < EARLIER([ID])) VAR __pos = COUNTX(FILTER(__table,[Value]<__value),[ID]) VAR __neg = COUNTX(FILTER(__table,[Value]>__value),[ID]) RETURN __pos - __neg) RETURN SUMX(__table1,[__S])

Enjoy!

eyJrIjoiODZmN2UzMDktNmUwMy00ODdmLWE1YzMtZDY2MDc0N2ZjOTdhIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9

Proud to be a Datanaut!

Route217

Member

Re: Mann-Kendall Test

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-04-2019
05:40 AM

Hi Greg

Firstly, wow and i am lost for words. The reason i reached out to you, is because you are the guy/person we reach out to when all hope is lost in the word or Power BI.

thank you and god bless.

A master at work.

Route217

Member

Re: Mann-Kendall Test

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-06-2019
04:34 AM

Hi Greg

I managed to copy use your excellent work to work out the __S for my data and the measure is.

__s = VAR ComplaintsByFiscalMo = ADDCOLUMNS(VALUES(PMS_COMPLAINT[FISCAL_MON_START_DT]),"CountComplaints", CALCULATE(COUNTROWS(PMS_COMPLAINT))) RETURN SUMX(ComplaintsByFiscalMo, SUMX(ComplaintsByFiscalMo, IF([FISCAL_MON_START_DT]>EARLIER([FISCAL_MON_START_DT])&&[CountComplaints]>EARLIER([CountComplaints]), 1, IF([FISCAL_MON_START_DT]>EARLIER([FISCAL_MON_START_DT])&&[CountComplaints]<EARLIER([CountComplaints]), -1)) ) )+0

I cannot for the love of god get the __Freq using my data.

i have a sample file here: https://www.dropbox.com/s/bpvte6ms66wv8u5/apples.pbix?dl=0

Greg_Deckler

Super User

Re: Mann-Kendall Test

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-06-2019
12:51 PM

Hmm @Route217, try this for frequency:

__freq = VAR ComplaintsByFiscalMo = ADDCOLUMNS(VALUES(PMS_COMPLAINT[FISCAL_MON_START_DT]),"CountComplaints", CALCULATE(COUNTROWS(PMS_COMPLAINT))) VAR __table = GROUPBY(ComplaintsByFiscalMo,[CountComplaints],"__ties",COUNTX(CURRENTGROUP(),[FISCAL_MON_START_DT])) VAR __table1 = ADDCOLUMNS(__table,"__ties1",[__ties] - 1) VAR __table2 = ADDCOLUMNS(__table1,"__freq",IF([__ties1]=0,0,[__ties1]*([__ties1]+1)*(2*[__ties1]+7))) RETURN SUMX(__table2,[__freq])

I should probably break my bad habit of using SUMMARIZE.

Proud to be a Datanaut!

Route217

Member

Re: Mann-Kendall Test

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-06-2019
11:31 PM

Hi Greg, aka DAX Guru.

the dax is spot on an gives the correct end result.

The only problem i can see with the whole Mann Kandell calculation for __s and __freq is when you have blank data points.

In R you have to input 12 data point for 12 months and months with no data are shown as 0 not blank. **So how do you get around this in DAX or how can i amend my formula to add 0 to the table below.**

R Input top part of screen and results bottom part.

Power BI Issues to over come

The Mann kandell falls apart when you have less then 12 data points. if we have 12 data points all the values are 100% spot on with R.

Greg_Deckler

Super User

Re: Mann-Kendall Test

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-07-2019
08:36 AM

If you need the freq measure to have a value of 0 instead of BLANK for the values in question, you should be able to do this:

__freq = VAR ComplaintsByFiscalMo = ADDCOLUMNS(VALUES(PMS_COMPLAINT[FISCAL_MON_START_DT]),"CountComplaints", CALCULATE(COUNTROWS(PMS_COMPLAINT))) VAR __table = GROUPBY(ComplaintsByFiscalMo,[CountComplaints],"__ties",COUNTX(CURRENTGROUP(),[FISCAL_MON_START_DT])) VAR __table1 = ADDCOLUMNS(__table,"__ties1",[__ties] - 1) VAR __table2 = ADDCOLUMNS(__table1,"__freq",IF([__ties1]=0,0,[__ties1]*([__ties1]+1)*(2*[__ties1]+7))) VAR __sum = SUMX(__table2,[__freq]) RETURN IF(ISBLANK(__sum),0,__sum)

Proud to be a Datanaut!

Route217

Member

Re: Mann-Kendall Test

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-07-2019
09:33 AM

Let me run this against R. And compair outputs..many thanks.

Route217

Member

Re: Mann-Kendall Test

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-07-2019
11:11 PM

Give this man a knigth hood.............................................................

ok one last quesion, on your original dash board as below how can you add the mann Kandell Tau calculation. see below. same data.....same values.

Greg_Deckler

Super User

Re: Mann-Kendall Test

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-08-2019
06:28 AM

Assuming that you are referring to Kendall's Tau Quick Measure here: https://community.powerbi.com/t5/Quick-Measures-Gallery/Kendall-s-Tau/m-p/625107.

The short answer is that given the original data that I used for this Mann-Kendall Test, I don't believe I can add Tau to it. The reason is that Kendall's Tau requires two sets of values. In the example above, I used "Interviewer1" and "Interviewer2". These were both columns of values. In the Mann-Kendall Test original data, I only have 1 set of values.

Sooo...I could add another set of values to the data and go that route. Otherwise, if I were to use the single column of values for both concordant and discordant pairs, I would end up with a calculation of 1 for Kendall's Tau. Of course, I wouldn't need to do the calculation in that case, I would just create a measure like Tau = 1.

Proud to be a Datanaut!

Route217

Member

Re: Mann-Kendall Test

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-08-2019
06:39 AM

Hi Greg

See file sample data where we could use the data and Material_id as two variables. from the main table PMS_Complaints. use material_id and the date column FISCAL_MON_START_DT

https://www.dropbox.com/s/i0nffe5gso2d684/Sample__.pbix?dl=0