creating a dynamic table in Power BI

[Anonymous]

Hi Experts

I am trying to creat the following in Power BI for the Y Values (dates) as shown in the image below. The image below is an example that someone has done in excel. The question is how would you do this in power BI

image 1.

i just need the result from the table which in excel is sum D4:015 = -44 (based on material_id using my data set) 

Date Table in Power BI

sample power bi file.

https://www.dropbox.com/s/1jq4ddeuy3nxpzq/apples.pbix?dl=0

[Cmcmahan · Accepted solution]

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.

[Cmcmahan]

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

[Anonymous]

The above was an example. Re My question is how is the order of the data determined? In my data set using material_id

[Anonymous]

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.

[Cmcmahan]

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.

[Anonymous]

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/

[Cmcmahan]

OK. I'm not a statistics person, so let's break this down.  If I've gotten any of the top-level bullet points wrong, please let me know.  

• You want a measure to calculate the MK-stat for some data. The previous answer I gave gets the correct answer for the small example data, so now we just need to adjust it for your large dataset.
• This data to be used is a subset of your data, using only data from the previous 12 months
  • What is the field for determining whether a row is in the last 12 months? In the PMS_COMPLAINT table, the options I see are:  'COMPLAINT_REPORTED_DT', 'REPORTED_FISCAL_YEAR_PD_SK', 'COMPLAINT_CLOSURE_DT', 'FISCAL_MON_START_DT', or is it some other field?
• The MK test seems to assume your data is in chronological order.  That's fine, and probably means we want to order by the date instead of by MATERIAL_ID. 
  • Is this the same date we use to determine if a row counts as in the last 12 months, or a different one?
  • It seems likely that you'll still want to group the data by MATERIAL_ID. This is fine, we just have to pick a way to aggregate the values within a given group.
• In the link, the values used in the MK test were just provided. We need to determine a way to calculate these values for your data so we can then use it as input for the MK test.
  • I see a staggering number of possibilities for what this input value could be.  It could be a count of complaints on each date, it could be a sum of QTY_AFFECTED on each date, it could be an average of ComplaintsY/X/W for each date, or something completely different.  
  • I have absolutely no idea which fields/groupings/aggregations you want to use to generate these values. This is where I need the most input from you.  If it helps, the link you shared had a line graph plotting the given values.  What fields/measures would you use as the X and Y axis for a similar graph with your data?
• Once you are able to correctly calculate the MK-stat (-44 in the example data), how do you want to display it in the report? In a table visual with MATERIAL_ID? In some sort of graph?

Also, if you copy-paste this to answer the questions, could you change the text color of your responses so that it stands out more? I don't want to miss anything.

[Anonymous]

OK. I'm not a statistics person, so let's break this down.  If I've gotten any of the top-level bullet points wrong, please let me know.  

• You want a measure to calculate the MK-stat for some data. The previous answer I gave gets the correct answer for the small example data, so now we just need to adjust it for your large dataset - Yes my data set PMS_Complaimts
• This data to be used is a subset of your data, using only data from the previous 12 months - and 24 and 36 months , this is shown by the Y and X and W letters in PMS_Financials Table
  • What is the field for determining whether a row is in the last 12 months? In the PMS_COMPLAINT table, the options I see are:  'COMPLAINT_REPORTED_DT', 'REPORTED_FISCAL_YEAR_PD_SK', 'COMPLAINT_CLOSURE_DT', 'FISCAL_MON_START_DT', or is it some other field?
• The MK test seems to assume your data is in chronological order.  That's fine, and probably means we want to order by the date instead of by MATERIAL_ID. - That is correct or we could use the date field as above
  • Is this the same date we use to determine if a row counts as in the last 12 months, or a different one? - Yes
  • It seems likely that you'll still want to group the data by MATERIAL_ID. This is fine, we just have to pick a way to aggregate the values within a given group. - Yes, and also but would it be possible to work this out based on what every filter i passed into the data set. (Seperate question)
• In the link, the values used in the MK test were just provided. We need to determine a way to calculate these values for your data so we can then use it as input for the MK test. if you scroll down i the link the calculation are there. see link again http://www.real-statistics.com/time-series-analysis/time-series-miscellaneous/mann-kendall-test/ 
  • I see a staggering number of possibilities for what this input value could be.  It could be a count of complaints on each date, it could be a sum of QTY_AFFECTED on each date, it could be an average of ComplaintsY/X/W for each date, or something completely different.  - Just the count of complaints by date
  • I have absolutely no idea which fields/groupings/aggregations you want to use to generate these values. - material_id, click on filter Manufacturing site, Region, Country, REPORTED_FAILURE_MD_CD and Reportability (dounut chart), just thses fields, if it can be done, allows me to work out the P-value for any combination.
  •  This is where I need the most input from you.  If it helps, the link you shared had a line graph plotting the given values.  What fields/measures would you use as the X and Y axis for a similar graph with your data? - look at the graph called Total compliants with trends.
• Once you are able to correctly calculate the MK-stat (-44 in the example data), how do you want to display it in the report? In a table visual with MATERIAL_ID? In some sort of graph? - See tooltip 1(second tab). The graph "Total complaints with trend" gives us the data point that you could put into R and check that your p-value is correct. Those are the data points for 12 months for a given product.

[Cmcmahan]

There we go. Now that I know what values you want input into the MK test, we can use it in the context of your report:

MK-Stat = 
VAR ComplaintsByFiscalMo = SUMMARIZECOLUMNS(PMS_COMPLAINT[FISCAL_MON_START_DT],"MonthGroup", SELECTEDVALUE(PMS_COMPLAINT[FISCAL_MON_START_DT]),"CountComplaints", COUNTROWS(PMS_COMPLAINT))
RETURN
SUMX(ComplaintsByFiscalMo, 
    SUMX(ComplaintsByFiscalMo,
        IF([MonthGroup]>EARLIER([MonthGroup])&&[CountComplaints]>EARLIER([CountComplaints]), 1,
        IF([MonthGroup]>EARLIER([MonthGroup])&&[CountComplaints]<EARLIER([CountComplaints]), -1))
    )
)

[Anonymous]

sorry to be a pain

but i cannot get this to work, can you kindly upload the pbix, please.

[Anonymous]

HI

do i add this as new table or a measure into PMS_Complaints table????

[Cmcmahan]

I created this as a measure in your _Measures table.

Here's the .pbix file.  The measure is MK-Stat, and I've displayed it in a card in the bottom right, just under MATERIAL_ID_FREQUENCY

[Anonymous]

Hi I did the same and it produced and error. Hmmmm.

[Cmcmahan]

What error are you receiving?

[Cmcmahan · Accepted solution]

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.

[Anonymous]

Hi silly question pms complainants Fiscal month st date is this for 12 month or 24 or 36.....what period.

[Cmcmahan]

You tell me. I figured it was just the start date of the current fiscal month that the complain was registered, and that if you changed your filter, it would include values from other periods.

So it should apply to whatever the filtered period is I guess?  I haven't messed with however you set that up.

[Anonymous]

Hi Mcmahan...

did you get my post on the frequncy question?

[Anonymous · Accepted solution]

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.

[Cmcmahan]

I'm able to get the same p-values, though I had to set up a new measure that mimicked the calculation the website was doing to get the value in P19.  They only match when you don't have any filters applied. I assume this is how they were originally calculated.

Also, due to an overflow error, the p-value can't be displayed in the Product Complaints Totals with Trend Direction matrix, so I displayed it in a final card. You can check that it's getting the correct value by selecting a single product from the matrix.

I've re-uploaded the new file to GDrive, feel free to test it out!