Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
Expand all | Collapse all

Running Total - for all columns

  • 1.  Running Total - for all columns

    Posted 9 days ago
    Team



    I'm creating a sample report, to show the running total. Below dax is working fine







    After adding some other dimension then the Running total is not showing as expected





    What could be the reason and help me to fix this. i'm sure someone might faced this issue before.



    Regards











    ------------------------------
    Kumar Kumar
    ------------------------------


  • 2.  RE: Running Total - for all columns

    Posted 8 days ago
    Hi, you need to remove the filters from the columns REFID and REFTYPENAME using REMOVEFILTERS inside CALCULATE in addition to what you have already written.

    Regards

    ------------------------------
    Francesco Bergamaschi
    Consultant & Professor
    ------------------------------



  • 3.  RE: Running Total - for all columns

    Posted 8 days ago
    Dear Francesco

    I know if I remove this will work. My requirement is how can build Dax for  any columns or multiple columns

    Hope you understand what I am looking
    Regards
    Kumar 






  • 4.  RE: Running Total - for all columns

    Posted 8 days ago
    Hi again
    In this case you should add a call to REMOVEFILTERS (    )

    Let mw know if it works

    Best,

    ------------------------------
    Francesco Bergamaschi
    Consultant & Professor
    ------------------------------



  • 5.  RE: Running Total - for all columns

    Posted 7 days ago

    Dear Francesco

    when i include the removefilter(), i'm not getting the expected result. It is calculating all the qty in the table.





    ------------------------------
    Kumar Kumar
    ------------------------------



  • 6.  RE: Running Total - for all columns

    Posted 7 days ago
    Hi,
    Now the last step is adding a call to VALUES ( column1, column2,...) where column1 etc are those you want to keep the filter on. There must have been a filter before that now we canceled with REMOVEFILTERS, otherwise qtys cannot be bigger than before. Pls check what filter is applied to the matrix, maybe from the filter panel or another visual. Otherwise pls send me the pbix

    ------------------------------
    Francesco Bergamaschi
    Consultant & Professor
    ------------------------------



  • 7.  RE: Running Total - for all columns

    Posted 7 days ago
      |   view attached
    Dear Francesco

    i could not able fix it.

    there are some reason i'm not using the matrix visual and i'm using Table visual (Repeat row values like in excel).

    here i have attached the sample files for your advise and kindly check the measure "RT_QTY V2"

    Regards
    Kumar


    ------------------------------
    Kumar Kumar
    ------------------------------

    Attachment(s)

    pbix
    MRP Working.pbix   890 KB 1 version


  • 8.  RE: Running Total - for all columns

    Posted 7 days ago
    You have filters on the top of the page, as I told you there must have been some

    Use this code, regards

    RT_QTY V2 =

    VAR _MaxDate = MAX(fct_ReqTrans[REQUIREMENTDATE])

    VAR _Result =
    CALCULATE(
        SUM(fct_ReqTrans[QTY]),
        FILTER( ALL(fct_ReqTrans[REQUIREMENTDATE]),
            fct_ReqTrans[REQUIREMENTDATE] <= _MaxDate
        ),
        REMOVEFILTERS(dimDates),
        ALLSELECTED()

    )
    RETURN
       _Result




    ------------------------------
    Francesco Bergamaschi
    Consultant & Professor
    ------------------------------



  • 9.  RE: Running Total - for all columns

    Posted 7 days ago
    Dear Francesco

    Thanks, it is working, but the result should below right, why it is taking whole qty for the day



    ------------------------------
    Kumar
    ------------------------------



  • 10.  RE: Running Total - for all columns

    Posted 7 days ago
    Because it is removing all filters apart from the day, as you requested. My impression is that you need to decide what you want to see when you change the columns set included in the visual. I remain at disposal

    ------------------------------
    Francesco Bergamaschi
    Consultant & Professor
    ------------------------------



  • 11.  RE: Running Total - for all columns

    Posted 5 days ago
    Ciao Kumar,
    I think I got it now. Try this

    RT_QTY V2 =
    VAR MaxDate =
        MAX ( fct_ReqTrans[REQUIREMENTDATE] )
    VAR DatesToUse =
        FILTER (
            ALL ( fct_ReqTrans[REQUIREMENTDATE] ),
            fct_ReqTrans[REQUIREMENTDATE] <= MaxDate
        )
    VAR CurrentREFID =
        SELECTEDVALUE ( fct_ReqTrans[REFID] )
    VAR ProductsOfTheCurrentDate =
        CALCULATETABLE (
            VALUES ( fct_ReqTrans[REFID] ),
            REMOVEFILTERS ( fct_ReqTrans[REFID] )
        )
    VAR ProductsOfTheCurrentDateAndRank =
        ADDCOLUMNS (
            ProductsOfTheCurrentDate,
            "@CR", RANKX ( ProductsOfTheCurrentDate, fct_ReqTrans[REFID],, ASC )
        )
    VAR CurrentIDRankOfTheCurrentDate =
        IF (
            SUM ( fct_ReqTrans[QTY] ) <> 0,
            SELECTCOLUMNS (
                FILTER ( ProductsOfTheCurrentDateAndRank, fct_ReqTrans[REFID] = CurrentREFID ),
                "@Rank", [@CR]
            )
        )
    VAR ProductsToFilter =
        CALCULATETABLE (
            SUMMARIZE ( fct_ReqTrans, fct_ReqTrans[REFID], fct_ReqTrans[REQUIREMENTDATE] ),
            REMOVEFILTERS ( fct_ReqTrans[REFID] ),
            DatesToUse
        )
    VAR ProductsToFilterAndRank =
        NATURALLEFTOUTERJOIN ( ProductsToFilter, ProductsOfTheCurrentDateAndRank )
    VAR ProductsFiltered =
        FILTER (
            ProductsToFilterAndRank,
            fct_ReqTrans[REQUIREMENTDATE] <> MaxDate
                || [@CR] <= CurrentIDRankOfTheCurrentDate
        )
    VAR Result =
        CALCULATE (
            SUM ( fct_ReqTrans[QTY] ),
            DatesToUse,
            ALLSELECTED (),
            ProductsFiltered
        )
    RETURN
        Result

    Does this satisfy you? It is rather complex but it works for me

    Best,
    Francesco


    ------------------------------
    Francesco Bergamaschi
    Consultant & Professor
    ------------------------------



  • 12.  RE: Running Total - for all columns

    Posted 5 days ago
    Dear Francesco,

    Thanks for trying to solve my issue. But unfortunately the above solution is not working as per my expectations.

    My calculation should be as per below. I have created new measure RT_QTY V3 as per above your code.

    REFTYPENAME REFID REQUIREMENTDATE ReqTrans_Qty Qty_RT RT_QTY V2 RT_QTY V1 RT_QTY V3 Expected
    On-hand   1/1/1900 -66 -66 -66 0 -66 -66
    Sales order 000705 12/14/2012 -31 -31 -140 -109 -31 -97
    Sales order 000707 12/14/2012 -25 -25 -140 -115 -56 -122
    Sales order 000719 12/14/2012 -18 -18 -140 -122 -74 -140
    Purchase order 000021 12/17/2012 4 4 -136 -140 4 -136
    Sales order 000699 12/21/2012 -25 -25 -161 -136 -99 -161
    Purchase order 000020 12/26/2012 2 2 -159 -161 6 -159
    Sales order 000698 12/27/2012 -25 -25 -184 -159 -124 -184
    Demand forecast   1/15/2013 -243 -243 -253 -10 -243 -427
    Sales order 000727 1/15/2013 -36 -36 -253 -217 -160 -463
    Sales order 000728 1/15/2013 -36 -36 -253 -217 -196 -499
    Planned purchase orders 003026 1/15/2013 3 3 -253 -256 3 -496
    Planned purchase orders 003027 1/15/2013 243 243 -253 -496 246 -253
    Sales order 000729 1/22/2013 -50 -50 -278 -228 -246 -303
    Planned purchase orders 003028 1/22/2013 25 25 -278 -303 271 -278
    Sales order 000731 1/24/2013 -36 -36 -296 -260 -282 -314
    Planned purchase orders 003029 1/24/2013 18 18 -296 -314 289 -296
    Sales order 000732 1/28/2013 -44 -44 -318 -274 -326 -340
    Planned purchase orders 003030 1/28/2013 22 22 -318 -340 311 -318
    Demand forecast   2/15/2013 -307 -550 -318 -11 -550 -625
    Planned purchase orders 003031 2/15/2013 307 307 -318 -625 618 -318
    Sales order 000733 2/18/2013 -50 -50 -343 -293 -376 -368
    Planned purchase orders 003032 2/18/2013 25 25 -343 -368 643 -343
    Demand forecast   3/15/2013 -322 -872 -665 -343 -872 -665
    Demand forecast   4/15/2013 -321 -1193 -986 -665 -1193 -986
    Demand forecast   5/15/2013 -322 -1515 -1308 -986 -1515 -1308

    Please check.

    ------------------------------
    Kumar
    ------------------------------



  • 13.  RE: Running Total - for all columns