Measure columns one sum multiple columns

Power BI
1

Does anyone know why measures sum as a group in table visuals? Is there a way to change this behavior? Here is an example, It has 5 measure columns that only display one total under the last column. That total is the sum of all five columns.

image
image1468×516 76.8 KB

I would like to have a sum under each column that only sums the column above it.

2

Hi @michael.wescott7226 ,

It usually happens due relationship and context problem.
To help further could you provide sample Excel and pbix file ?

Thank you.

3

Ar_Aging_Report.pbix (14.9 MB)
Attached is the PBIx file for the project I am working on. The bottom table is the one in question. I have seen this on other reports too.

4

In table you have context columns Invoice number, DueDate,
in Total you do not have that kind of context - you have total.

So for total InvoiceAge Today > 120 and all amount are display in this category:

120 days = IF([Invoice Age Today] > 120, IF([AmountOwed]>0,[AmountOwed],BLANK()),BLANK())

5

Do I understand you correctly? Are you saying that for the measures to show totals correctly in the table, all columns in the table must be in the measure?

6

@michael.wescott7226 ,

Measures are calculated based on the contexts.

You need to bring context to total measure, too.

7

I am just getting back to this today. I still don’t understand what you are trying to tell me. I did however find out that the only one that has a total on it is the one looking for all the largest values. If I just turn the > sign around in this expression before the 120 it doesn’t return a sum: 120 days = IF([Invoice Age Today] > 120, IF([AmountOwed]>0,[AmountOwed],BLANK()),BLANK())

8

There is no total measure. All the measures 0-30, 31-60, 61-90, 91-120, and 120 days are individual measures that should all have a sum under them.

9

Data mentor can very likely help solve this

I started something on this for you

10

See if it helps

0-30 days =
SUMX (
    SUMMARIZE (
        'OINV A/R Invoice',
        'OINV A/R Invoice'[DocNum],
        'OINV A/R Invoice'[CardName],
        'OINV A/R Invoice'[DocTotal]
    ),
    CALCULATE (
        IF (
            [Invoice Age Today] <= 30,
            IF ( [AmountOwed] > 0, [AmountOwed], BLANK () ),
            BLANK ()
        )
    )
)

image
image769×268 23 KB

11

Thank you. That worked well in that PBI report. I have another one I tried a variation of it in and it did not work though. Can you take a look at this measure and tell me why this one does not show a sum and the other one did?

Usage last 12 months =
SUMX (
SUMMARIZE (
‘OITW: Inventory Information’,
‘OITM: Item Information’[ItemCode],
‘OITW: Inventory Information’[WhsCode],
‘OITM: Item Information’[ItemName],
‘OITM: Item Information’[OnHand]
),
CALCULATE (
SUMX ( OINM, OINM[OutQty] * OINM[Price] ),
FILTER ( ‘OITM: Item Information’, ‘OITM: Item Information’[PrcrmntMtd] = “B” ),
FILTER ( OINM, OINM[TransType] = 60 || OINM[TransType] = 13 ),
FILTER (
OINM,
NOT (
CONTAINSSTRING ( OINM[JrnlMemo], “good” )
|| CONTAINSSTRING ( OINM[JrnlMemo], “cancel” )
)
),
FILTER ( ‘Date’, ‘Date’[Date] <= TODAY () && ‘Date’[Date] >= TODAY () - 365 ),
FILTER (
OINM,
OINM[Warehouse] = SELECTEDVALUE( ‘OITW: Inventory Information’[WhsCode] )
)
)
)