Power BI Matrix one sum stays blank. Why?

[Hierkommtnils]

Hi, I use to work with data from two different reporting dates in my data model based on the underlying data structure in our database. I have implemented a logic which takes 2023 delivery volumes from report date 31.12.2023 (as this data will not be 100% available in latest report date) and latest report date for >= 2024 deliveries. Here is how it looks in Excel and should be look in PBI as well:

For some reason, the highlighted sum is not shown in PBI. All the other sums and the detail rows are shown correctly.

Any idea whats wrong here?

I have tried several formulas but non of them fixed this issue. 

Formula at the moment:

VOLUME_PHYS_abs = SUMX(SUMMARIZE(FCT_PNL_DEAL,DIM_PAYMENT[SETTLEMENT_TYPE],DIM_PAYMENT[EVENT_SOURCE],FCT_PNL_DEAL[METRIC_DATE],DIM_DEAL_ATTRIBUTE[Fut_For],DIM_DEAL_ATTRIBUTE[BUY_SELL],DIM_TRADE_DATE[TRADE_YEAR], DIM_DELIVERY_END_DATE[DELIVERY_END_YEAR],DIM_PARTY_INTERNAL[BUNIT_NAME], DIM_PAYMENT[CFLOW_TYPE],DIM_DEAL_ATTRIBUTE[INSTRUMENT], DIM_REFERENCE[CASCADING_STATUS],DIM_DEAL_ATTRIBUTE[Intra_Select],DIM_REVAL_TYPE[REVAL_TYPE]),[Abs_VOLUME])

It is basically a SUMX SUMMARIZE function which sums up the Meassure "Abs_VOLUME" where I put in all the columns I use to filter or to detail the visulisation in the first part of the formula.

BR

Nils

[Anonymous · Accepted solution]

Hi @Hierkommtnils 

For your question, here is the method I provided:

The presentation of data in power bi and excel will be different, the style in your screenshot may not be possible, but I have another way for your reference.

Here's some dummy data

“Table”

Create a new table.

Table 2 = 
UNION(
SELECTCOLUMNS(
    'Table', 
    "type", 'Table'[type],
    "type_pay", 'Table'[type_pay],
    "year", 'Table'[year],
    "value",SWITCH(TRUE(), 'Table'[year] = 2023, 'Table'[value], 'Table'[year] <> 2023, 0)),
    SELECTCOLUMNS(
    'Table', 
    "type", "_" & 'Table'[type],
    "type_pay", 'Table'[type_pay],
    "year", 'Table'[year],
    "value",SWITCH(TRUE(), 'Table'[year] = 2023, BLANK(), 'Table'[year] <> 2023, 'Table'[value]))
)

You can get the table like this.

Create a matrix.

Here is the result.

Regards,

Nono Chen

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