Total of all minus total of non-blank

[Anonymous]

Dear Community, 

I would me very grateful if someone could give me a hint on how to solve the following problem:

I imported a table in PBI that looks something like this:

Column "EUR/T" indicates bonus per ton of goods sold, meaning that, where construction site is not blank - apply 1,5 eur/t bonus, and where it is blank - it means all the rest of the construction sites get 1 eur/t bonus.

How to create a DAX that would basically say, if construction site is blank, then take the total of all quantities sold per given customer and subtract from it the quantities of non-blank construction sites of the same customer?

I´d be extremely grateful if someone would take a look at this.

Thanks a lot!!

Nora

[v-luwang-msft · Accepted solution]

Hi @Anonymous ，

See the below:

Step ,import data:

Step 2,get Eur/t:

Eur/t = IF('Table'[Construction Site]=BLANK(),1,1.5)

Step 3, use the following measure then create visual:

test = 
CALCULATE (
    SUM ( 'Table'[Eur/t] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Customer_Nr] = MAX ( 'Table'[Customer_Nr] )
            && 'Table'[Construction Site] <> BLANK ()
    )
)
    - CALCULATE (
        SUM ( 'Table'[Eur/t] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Customer_Nr] = MAX ( 'Table'[Customer_Nr] )
                && 'Table'[Construction Site] = BLANK ()
        )
    )

Verification：

214214:8*1.5-1 =11

214215:2*1.5-1=2

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[VahidDM]

Hi @Anonymous 

Try this measure:

Measure = 
VAR _QTYB =
    CALCULATE (
        SUM ( 'Table'[quantities ] ),
        'Table'[construction site] = BLANK ()
    )
VAR _QTYNB =
    CALCULATE (
        SUM ( 'Table'[quantities ] ),
        'Table'[construction site] <> BLANK ()
    )
RETURN
    _QTYNB - _QTYB

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!

[Anonymous]

i @VahidDM , 

The output I get is the following

The total should be 269,5 , and the "blank" should be (269,5-23)=246,5.

Any idea of how to arrive there? 🙂

Kind regards

Nora

[Anonymous]

Hi Vahid, 

Thanks a lot for your reply!!

I have tried the same measure logic, but it doesn´t work for me and here is why - I didn´t mention that the quantities come from another table (from SQL server), and the table I showed in the post only serves to identify customers and respective construction sites that have these bonuses according to contract.

Since I connected the two tables by construction site columns, it is obviously a bit of an issue how to treat blank column values.. I hope I managed to explain it right..

[amitchandak]

@Anonymous , a new column

column =

if(isblank([construction site]) , sumx(filter(Table, [customer] =earlier([customer]) && not(isblank([construction site]) )) ,[Eur/t]) -sumx(filter(Table, [customer] =earlier([customer]) && (isblank([construction site]) )) ,[Eur/t]) ,[Eur/t])

[Anonymous]

Hi!

Thanks for your reply! I am still trying to implement your suggestion, and will let you know how it went!

Nora