CODEWITHSUNDEEP
powerbidax
CLT
CLT

Reputation: 21

Calculate total measure in PowerBI

I need help to create a measure that calculates totals in such a way that when I drag that measure to a table it is not affected by the filters of each row.

To explain it better, I'll give you an example. A company sells two products (tables and chairs) to two consumers:

https://i.sstatic.net/ZntaH.jpg

What I am looking for is a measure that would calculate the total for each product (8 chairs sold and 3 tables), regardless of the invoice or the customer:

https://i.sstatic.net/QFXZ7.jpg

Note that each column in the visualization belongs to different tables (related to each other). Thank you very much!!

UPDATE. These are the tables I am really working with:

CountOfPurchasetimes3 = 
VAR TblSummary =
ADDCOLUMNS(
    SUMMARIZE(
        fact_sale_invoice_line,
        fact_sale_invoice_line[product_id],
        fact_sale_invoice_line[customer_id]
    ),
    "TotalPurchase", 
    CALCULATE(
        COUNT(fact_sale_invoice_line[product_id]),
        ALLEXCEPT(fact_sale_invoice_line,fact_sale_invoice_line[product_id],fact_sale_invoice_line[customer_id])
    )
)

VAR count_times =
IF(
    CALCULATE(
        SUM('fact_sale_invoice_line'[product_quantity])>0,
        AND(
            fact_sale_invoice_line[invoice_date_id]>=20220101,
            fact_sale_invoice_line[invoice_date_id]<=20220131
        )
    ),
    CALCULATE(
        SUMX(TblSummary,[TotalPurchase]),
        AND(
            fact_sale_invoice_line[invoice_date_id]>=20220101,
            fact_sale_invoice_line[invoice_date_id]<=20220131
        )
    ),
    BLANK()
)
RETURN count_times

Upvotes: 0

Views: 253

Answers (1)

Ozan Sen
Ozan Sen

Reputation: 2615

2 methods to achieve your result:

Method_1

total unit by product = 
VAR TblSummary = ADDCOLUMNS(
    VALUES(YourTbl[product]),
    "Total", CALCULATE(SUM(YourTbl[units by invoice]),ALL(YourTbl),VALUES(YourTbl[product]))
)
RETURN
    SUMX(TblSummary,[Total])

Method_2

total unit by product = 
VAR TblSummary = ADDCOLUMNS(
    VALUES(YourTbl[product]),
    "Total", CALCULATE(SUM(YourTbl[units by invoice]),ALLEXCEPT(YourTbl,YourTbl[product]))
)
RETURN
    SUMX(TblSummary,[Total])

G1G1

To calculate purchase quantity:

CountOfPurchasetimes = 
VAR TblSummary = ADDCOLUMNS(
    SUMMARIZE(YourTbl,YourTbl[product],YourTbl[customer_id]),
    "TotalPurchase", CALCULATE(COUNT(YourTbl[product]),ALLEXCEPT(YourTbl,YourTbl[product],YourTbl[customer_id]))
)
RETURN
    SUMX(TblSummary,[TotalPurchase])

and lets test it on the visual:

DFGGG

Upvotes: 1

Related Questions