Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

creating a dynamic table in Power BI

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 woul...
Need Help
  • Cmcmahan's avatar
    Cmcmahan
    7 years ago

    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's avatar
    Anonymous
    7 years ago
    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's avatar
Cmcmahan
Resident Rockstar
7 years ago

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's avatar
Anonymous
Not applicable
7 years ago
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's avatar
    Cmcmahan
    Resident Rockstar
    7 years ago

    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!