CODEWITHSUNDEEP
excelpowerbidax
Fustavo Gringe
Fustavo Gringe

Reputation: 37

show data with no Filter in power BI

So, this is my query , sorry would not be able to provide my data

let's imagine there is a table called sales it has 4 columns (seller , quantiy, price, total price)

now i have few sellers whose price are yet to update in SQL but i have their Quantity how much they have sold. i have created dax query for total price which is simple sumx(quantity * price).

now here is my issue when i filter the table sales by a seller whose price isn't available in SQL it shows a blank table something like this

enter image description here

I at least want to show the shipped quantity if there is no price for that seller.

(P.s. Show items with no data isn't providing desirable results)

Upvotes: 0

Views: 1077

Answers (3)

Ozan Sen
Ozan Sen

Reputation: 2615

Please test this.

Please keep in mind that It is a table function (ADDMISSINGITEMS); so create a new table and paste the code below to see the result set.

(Table Function - You need to create a new table before pasting the below code:)

Show_Missing_Data =
ADDMISSINGITEMS (
    'FactTable'[sku],
    'FactTable'[shippedQty],
    SUMMARIZECOLUMNS (
        'FactTable'[sku],
        'FactTable'[shippedQty],
        "Total Sales", CALCULATE ( SUMX ( 'FactTable', [quantity] * [price] ) )
    ),
    'FactTable'[sku],
    'FactTable'[shippedQty]
)

If you want a measure to slice & dice in a table:

Total_Sales =
VAR AllSales =
    CALCULATE ( SUMX ( 'FactTable', [quantity] * [price] ) )
VAR Show_Missing_Data =
    ADDMISSINGITEMS (
        'FactTable'[sku],
        'FactTable'[shippedQty],
        SUMMARIZECOLUMNS (
            'FactTable'[sku],
            'FactTable'[shippedQty],
            "Total Sales", CALCULATE ( SUMX ( 'FactTable', [quantity] * [price] ) )
        ),
        'FactTable'[sku],
        'FactTable'[shippedQty]
    )
VAR Result =
    CALCULATE ( AllSales, Show_Missing_Data )
RETURN
    Result

Upvotes: 1

Mik
Mik

Reputation: 2103

SUMX(
    table
    ,quantity * if(
                   ISBLANK(price)
                   ,1
                   ,price
                )
)

Upvotes: 0

d b
d b

Reputation: 31

You could use the time-honored trick of adding 0 to the column/measure that is showing blank.

SellingPrice = SUM (SellingPrice) + 0

Upvotes: 2

Related Questions