Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

re write the following measures as variable

HI Experts

 

how could i consolidate and re write the following three meaure as a varibale as they are taking some time to compuate the output answer...

measure 1.

Cumulative Complaints = CALCULATE([TotalComplaintswithTrends],
FILTER(
ALLSELECTED(PMS_FINANCIAL_PDS),
PMS_FINANCIAL_PDS[Month Start] <= MAX(PMS_FINANCIAL_PDS[Month Start])
))
 
measure 2.
Cumulative Sales = CALCULATE([TotalSalesTrend],
FILTER(
ALLSELECTED(PMS_TM1_SALES_VOLUME),
[Month Start] <= MAX(PMS_FINANCIAL_PDS[Month Start])
))
 
resultant measure is
Cumulative Complaint Rate = IF(DIVIDE([Cumulative Complaints],[Cumulative Sales],0)<>0,DIVIDE([Cumulative Complaints],[Cumulative Sales],0))
+0
Need Help
  • v-yuta-msft's avatar
    v-yuta-msft
    6 years ago

    Anonymous ,

     

    Maybe the measure should be like below:

    Cumulative Complaint Rate =
VAR Cumulative_Complaints =
    CALCULATE (
        [TotalComplaintswithTrends],
        FILTER (
            ALLSELECTED ( PMS_FINANCIAL_PDS ),
            PMS_FINANCIAL_PDS[Month Start] <= MAX ( PMS_FINANCIAL_PDS[Month Start] )
        )
    )
VAR Cumulative_Sales =
    CALCULATE (
        [TotalSalesTrend],
        FILTER (
            ALLSELECTED ( PMS_TM1_SALES_VOLUME ),
            [Month Start] <= MAX ( PMS_FINANCIAL_PDS[Month Start] )
        )
    )
RETURN
    IF (
        DIVIDE ( Cumulative_Complaints, Cumulative_Sales, 0 ) <> 0,
        DIVIDE ( Cumulative_Complaints, Cumulative_Sales, 0 )

     

    Community Support Team _ Jimmy Tao

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Try

    //Option 1
averagex(summarize(FILTER(
ALLSELECTED(PMS_FINANCIAL_PDS),
[Month Start] <= MAX(PMS_FINANCIAL_PDS[Month Start])
),"_1",[TotalComplaintswithTrends],"_2",[TotalSalesTrend]),divide([_1],[_2]))

//Option 2
averagex(summarize(calculatetable(PMS_FINANCIAL_PDS,FILTER(
ALLSELECTED(PMS_FINANCIAL_PDS),
[Month Start] <= MAX(PMS_FINANCIAL_PDS[Month Start])
)),"_1",[TotalComplaintswithTrends],"_2",[TotalSalesTrend]),divide([_1],[_2]))
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago
      Hi Amit..
      The second messure reference the sales table, both measures are not the same PMS_Financial_PDS..
  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Anonymous ,

     

    Maybe the measure should be like below:

    Cumulative Complaint Rate =
VAR Cumulative_Complaints =
    CALCULATE (
        [TotalComplaintswithTrends],
        FILTER (
            ALLSELECTED ( PMS_FINANCIAL_PDS ),
            PMS_FINANCIAL_PDS[Month Start] <= MAX ( PMS_FINANCIAL_PDS[Month Start] )
        )
    )
VAR Cumulative_Sales =
    CALCULATE (
        [TotalSalesTrend],
        FILTER (
            ALLSELECTED ( PMS_TM1_SALES_VOLUME ),
            [Month Start] <= MAX ( PMS_FINANCIAL_PDS[Month Start] )
        )
    )
RETURN
    IF (
        DIVIDE ( Cumulative_Complaints, Cumulative_Sales, 0 ) <> 0,
        DIVIDE ( Cumulative_Complaints, Cumulative_Sales, 0 )

     

    Community Support Team _ Jimmy Tao

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.