new to BI, attempting something along the lines of a SUMIF i guess

[peterhinton]

so here goes, i have a table called LW, and collumn called SALES.QTY, and a column CALLED BRANCH.NO, there are around 1600 i want a third column that will give me the total SALES.QTY for the BRANCH.NO,

Each BRANCH.NO has more than one record of data in the table as it shows sales for different stock holding groups etc.

any adise on where to even begin looking on how to solve this or any good learning sites ?

[v-sihou-msft]

@peterhinton

In DAX, to achieve same logic like SUMIF, you just need to apply the condition to filter to table context in CALCUALTE() function. Just create a measure like:

Measure =
CALCULATE ( SUM ( LW[SALES.QTY] ), FILTER ( LW, LW[BRANCH.NO] = XXXX ) )

If you want to calculate the total for each BRANCH.NO, you need to use ALLEXCEPT() to have calculation group on BRANCH.NO. The formula can be like:

Measure =
CALCULATE ( SUM ( LW[SALES.QTY] ), ALLEXCEPT ( LW, LW[BRANCH.NO] ) )

Reference:

From SQL to DAX: Filtering Data

ALLEXCEPT Function (DAX)

Regards,

[kcantor]

@peterhinton

I would look at grouping the data within the query editor. You could duplicate the table first if you still need the "ungrouped" information. See here for more information: https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-common-query-tasks/

Group is a little ways down the page but should be what you are looking for.

[Phil_Seamark]

Hi @peterhinton and welcome to the community.

I believe this column will give you what you are after

New Column = CALCULATE(
            SUM(
                'LW'[SALES.QTY]),
                FILTER(
                    ALL('LW'),
                    LW[BRANCH.NO]=EARLIER('LW'[BRANCH.NO])
                    )
                   )

However adding a repeating column of data like this means you might end up duplicating this down the track so just keep that in mind. 🙂