Add from previous row value in a Matrix

[marcss44]

I've got a Matrix with months in the column header and days in the row header and the value are the items count.

For the 138 value on ene column and 1 row, i need to get the SUM of 138 and the previous value 537. And the recursively...

How can i do it?

[v-shex-msft]

Hi @marcss44 ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[TomMartens]

He @marcss44 ,

I do not download from we transfer, I'm sorry.

Regards,

Tom

[marcss44]

Sorry, from google Drive: https://drive.google.com/file/d/1bWSzMQ3p2vp2ZqH5fq5CuTLXGfXsbCNg/view?usp=sharing

[TomMartens]

Hey @marcss44 ,

based on your sample data I use two measures to create the Matrix visual:

1. A measure that does the distinctcount
   Because DISTINCTCOUNT is an expensive operation I use one of the optimization techniques, you can read about optimizing DISTINCTCOUNT here: Analyzing the performance of DISTINCTCOUNT in DAX - SQLBI
2. A measure that creates the RunningSum of the DistinctCounts across the Days
   This measure is still leveraging WINDOW
   
   

The DISTINCTCOUNT measure:

Some kind of DistinctCount = 
COUNTROWS(
    VALUES( 'SDFSDG'[PEDIDO] )
)

The RunningSum measure:

RunningSum Of DistinctCounts = 
[Some kind of DistinctCount]
+
SUMX(
    WINDOW(
        1 , abs
        , -1 , rel
        , SUMMARIZE(
            ALLSELECTED( 'TablaDifDias'[Value] )
            , 'TablaDifDias'[Value]
        )
        , ORDERBY( 'TablaDifDias'[Value] , ASC )
    )
    , [Some kind of DistinctCount]
)

And the Matrix visual:

The screenshot reveals an implicit challenge/isssue of RunningSums calculations. If you do not want to have a running sum calculated for days that do not have DISTINCTCOUNT then you can use the below measure:

smarter RunningSum Of DistinctCounts = 
var currentDistinctCount = [Some kind of DistinctCount]
return
IF( NOT(ISBLANK( currentDistinctCount ) )
    , currentDistinctCount
    +
    SUMX(
        WINDOW(
            1 , abs
            , -1 , rel
            , SUMMARIZE(
                ALLSELECTED( 'TablaDifDias'[Value] )
                , 'TablaDifDias'[Value]
            )
            , ORDERBY( 'TablaDifDias'[Value] , ASC )
        )
        , [Some kind of DistinctCount]
    )
)

Hopefully, this provides what you are looking for.

Regards,

Tom

[marcss44]

Yeah it works, and the lasti, how can i get the sum of all values in evrey column, for example 771 for the first column, 879 for the second...

[TomMartens · Accepted solution]

Hey @marcss44 ,

here you go:

TotalValue Of DistinctCounts = 
SUMX(
    ALLSELECTED( 'TablaDifDias'[Value] )
    , [Some kind of DistinctCount]
)

Regards,

Tom

[TomMartens]

Hey @marcss44

please create a pbix that contains sample data but still reflects your semantic model (tables, relationships, calculated columns, and measures). Upload the pbix to OneDrive, Google Drive, or Dropbox and share the link.

Regards,

Tom

[TomMartens]

Hey @marcss44 ,

assuming the table looks like this (I only entered a couple of values):

You can use the below measure to achieve what you want:

Measure = 
CALCULATE( SUM( 'Table'[value] ) )
+ CALCULATE(
    SUM( 'Table'[value] )
    , WINDOW(
        1 , abs
        , -1 , rel
        , SUMMARIZE(
            ALLSELECTED( 'Table'[day_diff] )
            , 'Table'[day_diff]
        )
        , ORDERBY( 'Table'[day_diff] , ASC )
    )
)

The measure is leveraging the windowing function WINDOW (WINDOW – DAX Guide😞

The Matrix visual will look like this:

I also wrote an article why WINDOW is my favorite windowing function: My favorite windowing function - WINDOW - Mincing Data - Gain Insight from Data (minceddata.info)

Hopefully, this provides what you are looking for, to tackle your challenge.

Regards,

Tom

[marcss44]

Just one step more, the value is the distinctcount of invoices not direct value