re write the following measures as variable

[Anonymous]

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

[v-yuta-msft · Accepted solution]

@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.

[amitchandak]

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]

Hi Amit..
The second messure reference the sales table, both measures are not the same PMS_Financial_PDS..