Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Calculation over rows with condition

Hello all.   I am trying to improve a file I have worked on. I need some calculations over rows with a condition, and I was not able to do them on the interface (DAX), so I created different tables...
General Comment
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    8 years ago

    Hi Anonymous,

     

    If you want to get the summaried stop time you can try to add new measure with below formula:

    Stop time(Summaried) =
VAR temp =
    ADDCOLUMNS (
        ALL ( Trips ),
        "Stoped Time", LOOKUPVALUE (
            Trips[TripEndDateTime],
            Trips[VehicleID], [VehicleID],
            Trips[Date], [Date],
            Trips[Index], [Index] - 1
        )
    )
RETURN
    SUMX (
        ADDCOLUMNS (
            FILTER ( temp, [VehicleID] = MAX ( [VehicleID] ) && [Date] = MAX ( [Date] ) ),
            "Diff", DATEDIFF ( [Stoped Time], [TripStartDateTime], SECOND )
        ),
        [Diff]
    )

     

    Regards,

    Xiaoxin Sheng

JosePintor's avatar
JosePintor
Frequent Visitor
7 years ago

Hi everybody,

 

I have a similar issue with calculate GrandTotal over a TOPN list that changes with drilldown of 4 dimensions.

how can i SUM only the visibles TOP5 values refering to the correspondent dimension at visible drilldown level.

 

Let's explain:

I have a matrix with 4 dimensions with drilldown (TipoArtigo; Marca; Familia; Artigo)

I want to put the totals in the middle column (wich is a measure of TOPN margin of the dimension that is in each level of drill down. Basically I need to have only one measure that works for all 4 drill downs dimensions.

I tried with SUMX and TOPN to achive Totals only for TOPN of each dimension, but I couldn’t be able to achieve that. The problem is that in totals we don’t have row context and don’t know what dimension is in matrix lines at each moment, to sum only TOPN (5 in case) for that dimension.

I have 4 measures (one for each dimension) that work ok only when matches with the dimension in drilldown, that's why I need a meaures for all dimensions.

1st column (TOPN HierarArtigo Margem) works ok in all dimensionsm but give the GrandTotal fo all the items and not only the visibles ones (TOP 5).

2nd column column (TOPN HierarArtigo Margem Parcial)  is the one o don’t work on totals only for TOP 5 itens.

3rd  column  (TOPN <dimension _name> Margem Parcial) work ok only with the corresponding dimension in lines otherwise gives only total (and wrong, because it’s the total that TOPN is supposed to calc based on the specific data_column).

In the follows print screens the 1st and 2nd measures area the same for all drilldowns, the 3rd changes….

Don’t worried because the grand total of the first column is lower than the parcial totals, because there are items with negative values (margin).

At the end I post all those measures.

 

 

 

How can I SUMX just the visibles rows in matrix depending on drilldown level selected by user, that changes the TOPN ( VALUES ( column) ) ?!

 

Thanks for help!

Regards,

José Pintor