Getting the sum of two invoice items at Invoice level

DAX DAX Calculations
1

I have a billing table
I have a measure
[Billing quantity] =
VAR NumberOfRows = COUNTROWS(‘Billing Table’)
RETURN
SUM(‘Billing Table’,DIVIDE(‘Billing Table’[Quantity],NumberOfRows)

I thought this worked but on finding two sales orders with different quantities this no longer works

The report Shows the calculation at row level.

How do I get the calculation to get the value for each sales order and then add those two values?

I managed to get as far as
VAR Selection =
SUMMARIZECOLUMNS(
‘Billing Table’[Sales Order]
,‘Billing Table’[Quantity]
)
But then I need to reference that to get the SUM of Selection[Quantity] and I can’t figure out that bit.

Or is there an easier way?
Thanks
E
Get

image

2

The solution that appears to work is

SUMX(
DISTINCT( ‘Billing Table’[Quantity].‘Billing Table’[Quantity])
)

So if we have ten different quantities it will add the ten,. However I think that if there are 11 sales orders on an Invoice and there are 10 different quantities then it wont work

3

Hello @ells,

Thank You for posting your query onto the Forum.

In order to achieve the results based on the scenario that you’ve provided in your original post. Below is the DAX measure alongwith the screenshot of the final results provided for the reference -

Total Quantity =
SUMX(
    ADDCOLUMNS(
        SUMMARIZE(
            'BillingTable_S1' ,
            'BillingTable_S1'[Invoice Number] ,
            'BillingTable_S1'[Sales Order] ) ,
        "@Total_Quantity" , 
        CALCULATE( MIN( 'BillingTable_S1'[Quantity] ) ) ) ,
    [@Total_Quantity] )

Final Results

I’m also attaching the working of the PBIX file for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

Sum of Total Quantities - Harsh.pbix (21.4 KB)

1 Like
4

@Harsh
Thanks - thats awesome.

I managed to get to
SUMX(
SUMMARIZE(‘Billing Table’,[Quantity],[sales order]),[Quantity])

Currently testing both solutions.
Thanks
E