CODEWITHSUNDEEP
powerbidax
Isaac Speelman
Isaac Speelman

Reputation: 1

Power BI - When column is summed, only some values sum and others do not

I have a dataset for a client where they are looking for the sales of each order for orders after a certain date billed and with null values.

This is an example of what the data looks like with [Sales] set to Do Not Summarize.

Order# Customer Date Billed Sales
1 Apple 1/1/2023 100
2 Verizon 150
2 Verizon -150
3 AT&T 1/2/2023 200
3 AT&T 1/2/2023 -50
4 Samsung 100

[Date Billed] in this case is a measure:

Date Billed = VAR Billed = CALCULATE( MAX(table1[Bill Date]), KEEPFILTERS(table2[Order#]) ) RETURN IF( ISBLANK( Billed ), "", Billed )

This was done because the order#'s would not show in a table if there were no value for [Date Billed]. We or the client do not have access to change the model/relationships. We do know that it is a many-to-many relationship between table1[Invoice#] and table2[Order#]. So those Order#'s without Invoice#'s do not show in the table without [Date Billed] measure.

What I need is for the table to return results like:

Order# Customer Date Billed Sales
1 Apple 1/1/2023 100
2 Verizon 0
3 AT&T 1/2/2023 150
4 Samsung 100

But what I am getting is:

Order# Customer Date Billed Sales
1 Apple 1/1/2023 100
2 Verizon
3 AT&T 1/2/2023 150
4 Samsung

Any help is appreciated.

Upvotes: 0

Views: 341

Answers (1)

Amira Bedhiafi
Amira Bedhiafi

Reputation: 1

The issue doesn't seem to be with your measure directly, but I would rather say that it is with how the sales are being aggregated and displayed in the context and the presence of null or blank values in the 'Date Billed' field.

Try the following :

Total Sales = 
SUMX(
    SUMMARIZE(
        MyTable, 
        MyTable[Order#], 
        "TotalSales", SUM(MyTable[Sales])
    ), 
    [TotalSales]
)

enter image description here

Upvotes: 0

Related Questions