Forum Discussion

Iron Contributor

Dec 21, 2022

Solved

CALCULATE function not allowed use in a TRUE/FALSE expression.

Hi, I would like to filter data with criteria : [Tỉ lệ PU đúng giờ]<"99%" I try to write a sample function as below in Power Pivot. Sample function:=CALCULATE(COUNTA(Data[Bưu cục gửi hàng]...

SampleCaculated 21.12.xlsx19.1 MB

excel

Formulas and Functions

SergeiBaklan

Jan 10, 2023

littlevillage

I combined initial measures into one, but you may return back

Intermediate count:=VAR filter1 =
    CALCULATETABLE (
        Data,
        Data[Tổng phải lấy] = "Lấy",
        OR (
            OR (
                OR ( Data[Mã khách hàng] = "084LC00010", Data[Mã khách hàng] = "084LC00016" ),
                OR ( Data[Mã khách hàng] = "084LC00005", Data[Mã khách hàng] = "084LC00053" )
            ),
            OR ( Data[Mã khách hàng] = "084LC00205", Data[Mã khách hàng] = "084LC00076" )
        )
    )
VAR filter2 =
    CALCULATETABLE ( Data, FILTER ( filter1, Data[PU đúng giờ] = "PU đúng giờ" ) )
VAR filter3 =
    CALCULATETABLE ( Data, FILTER ( filter1, Data[LATE] = "Trễ" ) )
VAR filter4 =
    CALCULATETABLE (
        Data,
        FILTER (
            filter3,
            OR (
                OR (
                    Data[Trạng thái đơn hàng] = "abnormal parcel",
                    Data[Trạng thái đơn hàng] = "DISPATCH"
                ),
                OR (
                    Data[Trạng thái đơn hàng] = "PICKUP FAILED",
                    Data[Trạng thái đơn hàng] = "PUSAT_DISPATCH"
                )
            )
        )
    )
VAR counting =
    IF (
        COUNTROWS ( filter2 ) / COUNTROWS ( filter1 ) < 0.99,
        COUNTROWS ( filter4 ),
        BLANK ()
    )
RETURN
    counting

Based on it final measure

Final count:=IF (
    HASONEVALUE ( Data[Bưu cục gửi hàng] ),
    [Intermediate count],
    SUMX ( Machinhanh, [Intermediate count] )
)

Result is

sample (1) (8).xlsx20 MB

littlevillage

Iron Contributor

Jan 08, 2023

SergeiBaklan

Thank you for response,

The logic like:

=SUMIFS(Data[Copy of Sample],Data[Mã khách hàng],(E10:E15),Data[Trạng thái đơn hàng],(F10:F15),Data[Tổng phải lấy],(G10:G15),Data[LATE],(H10:H15),Data[Tỉ lệ PU đúng giờ],"<99%")

How can we do the same that in Power Pivot,

SergeiBaklan

Diamond Contributor

Jan 10, 2023

littlevillage

I combined initial measures into one, but you may return back

Intermediate count:=VAR filter1 =
    CALCULATETABLE (
        Data,
        Data[Tổng phải lấy] = "Lấy",
        OR (
            OR (
                OR ( Data[Mã khách hàng] = "084LC00010", Data[Mã khách hàng] = "084LC00016" ),
                OR ( Data[Mã khách hàng] = "084LC00005", Data[Mã khách hàng] = "084LC00053" )
            ),
            OR ( Data[Mã khách hàng] = "084LC00205", Data[Mã khách hàng] = "084LC00076" )
        )
    )
VAR filter2 =
    CALCULATETABLE ( Data, FILTER ( filter1, Data[PU đúng giờ] = "PU đúng giờ" ) )
VAR filter3 =
    CALCULATETABLE ( Data, FILTER ( filter1, Data[LATE] = "Trễ" ) )
VAR filter4 =
    CALCULATETABLE (
        Data,
        FILTER (
            filter3,
            OR (
                OR (
                    Data[Trạng thái đơn hàng] = "abnormal parcel",
                    Data[Trạng thái đơn hàng] = "DISPATCH"
                ),
                OR (
                    Data[Trạng thái đơn hàng] = "PICKUP FAILED",
                    Data[Trạng thái đơn hàng] = "PUSAT_DISPATCH"
                )
            )
        )
    )
VAR counting =
    IF (
        COUNTROWS ( filter2 ) / COUNTROWS ( filter1 ) < 0.99,
        COUNTROWS ( filter4 ),
        BLANK ()
    )
RETURN
    counting

Based on it final measure

Final count:=IF (
    HASONEVALUE ( Data[Bưu cục gửi hàng] ),
    [Intermediate count],
    SUMX ( Machinhanh, [Intermediate count] )
)

Result is

sample (1) (8).xlsx20 MB

SergeiBaklan
Diamond Contributor
Jan 25, 2023
littlevillage

It depends on what you'd like to receive. Value in Grand Total has no connection with values in the column. Grand Total is calculated by separate formula, thus not necessary equal to the sum of column values.
littlevillage
Iron Contributor
Jan 25, 2023
SergeiBaklan
in the image below, I have added 2 fields (Mã vận đơn and Trạng thái đơn hàng), it still show 82 but actually, sum of Final count column is 85. May be 239D01 and 237H01 should be removed becasue "Tỉ lệ PU đúng giờ" > 99% then result is 82.
Hope for your response.
SergeiBaklan
Diamond Contributor
Jan 25, 2023
littlevillage

in Final count measure we summarize based on Machinhanh table

...SUMX ( Machinhanh, [Intermediate count] )

it doesn't matter will you add fields from Data table into PivotTable or not. Thus result is the same, 82.

Do you mean it shall be 85 if any field from Data table is added?
littlevillage
Iron Contributor
Jan 25, 2023
SergeiBaklan
Thank you very much,
Expected results are 100% correctly.
it's just that i still don't quite understand, if i add 2 fields "Mã vận đơn" and "Trạng thái đơn hàng" as shown then result still 85 instead of 82 because 239D01 and 237H01 have been added respectively.