Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Relationship issue with Measure

Hi Experts I have been looking at this problem for 3 days and cannot see why my table to the right does not show the same p-value for each of the month periods.  Scenario If you select product id ...
Need Help
  • freder1ck's avatar
    freder1ck
    6 years ago

     

    New Table

    My Table = 
VAR OuterComplaints = COUNTROWS ( PMS_COMPLAINT )
RETURN
ADDCOLUMNS (
    VALUES ( PMS_FINANCIAL_PDS[Month Start] ),
    "CountComplaints", CALCULATE ( COUNTROWS ( PMS_COMPLAINT ) ),
    "Index", RANKX ( PMS_FINANCIAL_PDS, PMS_FINANCIAL_PDS[Month Start],,, DENSE ),
    "Outer Complaints", OuterComplaints
)

    Something along these lines...

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

based on your excellent feedback, if you take from PMS_TM1_Sales_vol Table column (month_date) and add that to the table it give you the right p-value but errors on the inidivdual data points shows 846 as a constant number for 12 periods.

 

As opposed to the actual value (see image  below) - fixed one problem another one comes along.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    hi Freder1ck

    firstly many thanks, i believe the answer lies with the following measure.

     

    __s = VAR Mytable1 = ADDCOLUMNS(VALUES(PMS_FINANCIAL_PDS[Month Start]),"CountComplaints",CALCULATE(COUNTROWS(PMS_COMPLAINT)),"Index",RANKX(PMS_FINANCIAL_PDS,PMS_FINANCIAL_PDS[Month Start],,,Dense))
RETURN
SUMX(Mytable1, 
    SUMX(Mytable1,
        IF([Index]>EARLIER([Index])&&[CountComplaints]<EARLIER([CountComplaints]), 1,
        IF([Index]>EARLIER([Index])&&[CountComplaints]>EARLIER([CountComplaints]), -1))
    )
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Another expert also tried

    I've just tried to see what values EARLIER formula returns, and it seems like it returns the same value as if there were no EARLIER function.

     

    I created the measure below to check the output:

    __testS = 
VAR Mytable1 = ADDCOLUMNS(VALUES(PMS_FINANCIAL_PDS[Month Start]);"CountComplaints";CALCULATE(COUNTROWS(PMS_COMPLAINT));"Index";RANKX(PMS_FINANCIAL_PDS;PMS_FINANCIAL_PDS[Month Start];;;Dense))
VAR cc = 
SUMX(Mytable1; 
    SUMX(Mytable1;
        EARLIER([CountComplaints])
    )
)
VAR i = 
SUMX(Mytable1; 
    SUMX(Mytable1;
        EARLIER([Index])
    )
)
RETURN
    i & ", " & cc
    • freder1ck's avatar
      freder1ck
      Kudo Kingpin
      6 years ago
      Instead of EARLIER, you could set variables to fix values outside the SUMX. You could also preview the results of your MyTable by doing a new table from the modeling tab. You could also use ADDCOLUMNS to preview the amounts at the row level.
      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        hi freder1ck

         

        could you kindly demo this table - i follow what you are saying but slighly confused at the start. i'll happily add a demo file if you need one. thanks once again.