Calculate Sum of Column 1 only if Column 2 is not blank

[atult]

Hello Experts,
Data:

Expected Result:
- Calculate sum of Sales only if Units is not blank
- Sales Sum = 10 + 20 + 40 = 70; Sales = 30 for XYZ is skipped since Units is blank for it

I'm using below dax in calculated measure but it is not working:

Total Sales = IF(SUM('Sales_Table'[Units]) = BLANK(),BLANK(),SUM('Sales_Table'[Sales]))

[Greg_Deckler · Accepted solution]

@atult This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

[Anand24 · Accepted solution]

Hi @atult ,
Try below dax:

Total Sales = 
CALCULATE(
    SUM('Sales_Table'[Sales]), 
    'Sales_Table'[Units] <> BLANK()
)

For ease of Filtered Measure DAX, you can directly go to quick measure -> filtered value and then change your dax accordingly. 

Give a Thumbs Up if this post helped you in any way and Mark This Post as Solution if it solved your query !!! Proud To Be a Super User !!! LinkedIn

[Greg_Deckler]

@atult Perhaps try this:

Total Sales = IF(SUM('Sales_Table'[Units]) <= 0,BLANK(),SUM('F_ORDERED Sales_Table'[Sales]))

[atult]

Hi @Greg_Deckler ,
The total for Total Sales is still considering Sales value of XYZ:

[Greg_Deckler · Accepted solution]

@atult This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

[atult]

@Greg_Deckler ,
Tried the below but not helping:

Total Sales = 
IF(
    HASONEFILTER(Sales_Table[Category]),
    IF(
        SUM(Sales_Table[Units]) = BLANK(),0,SUM(Sales_Table[Sales])
        ),
        SUMX(
            FILTER(Sales_Table,
            [Units]>1000
            ),
        [Sales]
        )
)

[Greg_Deckler]

@atult Keep your original measure. Create a new measure:

Total Sales = 
IF(
    HASONEFILTER(Sales_Table[Category]),
    [Original Measure],
    SUMX(SUMMARIZE('Table',[Category],"__Sales",[Original Measure]),[__Sales])
)

For Measure Totals to work, you have to summarize or summarizecolumns or groupby your data exactly as it is in the visual and then use an X aggregator across that summarized table.