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.
The 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 |
- Many thanks for the amazing feedback. My question is how would I do that for my Y values in table above. Dates starting at 1.7.2018 to 1.6.2019.l for the provided data set using column MATERIAL_ID.. OR am I complete mad to use material Id..
Note as I move in August 2019 may dates change from 1.7.2018 to 1.8.2019 and 1.6.2019 to 1.17.2019 for period 12.What is a "Y value" from the table above? A year value? Some sort of calculated value? The data table you shared looks like a date dimension, so doesn't have any Material ID associated with the data, nor any actual data to calculate a "Y value" from.
You're fine using MATERIAL_ID as your sorting column, as long as that's the order you want the data in. I would have assumed that MATERIAL_ID is more of a group by field as opposed to an ordering field, but it could be both. From your original example, I wasn't sure why 6.8 came first, and 5.9 second. If that's because the MATERIAL_ID for 6.8 is less than the MATERIAL_ID of 5.9, that's fine.
Changing the date range of the current period should work fine for whatever visual you have, since that will limit the input data.
I would try and help more directly with the .pbix you shared, but there's SO MUCH data in there, I don't know what fields to use for what. That's why my previous answer used the very simple data set with 12 values that you shared. You should be able to just replace Data[Index] and Data[Value] with the fields/measures that you actually want to use for calculations.
What is a "Y value" from the table above? Y Vlaue just meand 12 months period in above table
A year value? Some sort of calculated value? The data table you shared looks like a date dimension, so doesn't have any Material ID associated with the data, nor any actual data to calculate a "Y value" from. the material ID is in the main FACT table PMS_Complaints
You're fine using MATERIAL_ID as your sorting column, as long as that's the order you want the data in. I would have assumed that MATERIAL_ID is more of a group by field as opposed to an ordering field, but it could be both. From your original example, I wasn't sure why 6.8 came first, and 5.9 second. If that's because the MATERIAL_ID for 6.8 is less than the MATERIAL_ID of 5.9, that's fine. - my original example was based on a totally different data set and was merely used as an example, as i wanted to replicate that method on the bigger picture PMS_Complaints
Changing the date range of the current period should work fine for whatever visual you have, since that will limit the input data.
I would try and help more directly with the .pbix you shared, but there's SO MUCH data in there, I don't know what fields to use for what. That's why my previous answer used the very simple data set with 12 values that you shared. You should be able to just replace Data[Index] and Data[Value] with the fields that you actually want to use for calculations. - the master FACT table is PMS_Complaints and i am trying to group the data using Material_ID and somehow work out the mann Kandell p-values (see link below)
http://www.real-statistics.com/time-series-analysis/time-series-miscellaneous/mann-kendall-test/
- The above was an example. Re My question is how is the order of the data determined? In my data set using material_id