Forum Discussion
creating a dynamic table in Power BI
If the error message is about SUMMARIZECOLUMNS and ADDMISSINGITEMS not being allowed in the current context, that's an issue with SUMMARIZECOLUMNS not playing well with slicers on multiple columns.
I re-wrote the measure to use the old ADDCOLUMNS style pattern that was used before SUMMARIZECOLUMNS was introduced:
MK-Stat = 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)) ) )I've also re-uploaded the change in the .pbix file on Google Drive. The same link will download the newer version.
- Hi thanks for the excellent feedback. I'll have to post another question as I have just applied your calculation in the SE for and the Z test as per link and I am getting a different p value to the one showing in the main fact table in column mannkandelY. See what se and z test values u get as per link.
Resident RockstarThe formula seems pretty easy. Take the count of rows that are below than the current row, and evaluate them for positive/zero/negative results. To break it down, if all you have is the data in A4:A15, get a count of values that have a higher row# and that are higher than the current value, and subtract a count of values that have a higher row# and are lower than the current value. Repeat for each row.
My question is how is the order of the data determined? Right now, it seems arbitrary, but changing the order would definitely give a different result. In this pseudocode, I've just given a numerical index to each data point, to keep the order as you presented it. You can replace that with any sortable column that indicates the order of the values.
MKSum =
SUMX(
'Data',
CALCULATE(COUNTROWS(FILTER(ALL(Data), Data[Index]>SELECTEDVALUE('Data'[Index]) && Data[Value] > SELECTEDVALUE(Data[Value])))) -
CALCULATE(COUNTROWS(FILTER(ALL(Data), Data[Index]>SELECTEDVALUE('Data'[Index]) && Data[Value] < SELECTEDVALUE(Data[Value]))))
)Here's the data table I used to get a result of -44:
Data:
| Value | Index |
| 6.8 | 1 |
| 5.9 | 2 |
| 5.7 | 3 |
| 5.5 | 4 |
| 5.5 | 5 |
| 4.5 | 6 |
| 4 | 7 |
| 5.1 | 8 |
| 4.5 | 9 |
| 4.5 | 10 |
| 3.3 | 11 |
| 4.8 | 12 |