Forum Discussion

Justas4478's avatar
Justas4478
Post Prodigy
1 year ago
Solved

ALL function with text

Hi, I have this dax measure: CALCULATE(SUM('Stock adjustment data'[Difference Quantity(PIC)]),ALL('Calendar'[Date])) I am trying to add more variables in to ALL function, but get this error when i...
Need Help
  • Justas4478's avatar
    Justas4478
    1 year ago

    FBergamaschi This is solution provided by copilot. It seams to work.
    I dont know if there is need to siplify it but here it is.

    VAR SummaryTable =
        CALCULATETABLE(
            SUMMARIZE(
                FILTER(
                    'Stock adjustment data',
                    'Stock adjustment data'[Physical Inventory Status] = "POST"
                ),
                'Product information'[Product],
                "TotalDiff", SUM('Stock adjustment data'[Difference Quantity(PIC)])
            ),
            REMOVEFILTERS('Calendar'[Date]), // ⬅️ This removes the impact of the date slicer
            ALL('Stock adjustment data')
        )

    VAR CurrentProduct =
        SELECTEDVALUE('Product information'[Product])

    VAR ProductTotal =
        CALCULATE(
            MAXX(
                FILTER(SummaryTable, [Product] = CurrentProduct),
                [TotalDiff]
            )
        )

    RETURN
        IF(
            ISINSCOPE('Product information'[Product]),
            IF(
                ProductTotal < 0,
                CALCULATE(
                    MINX(
                        FILTER(SummaryTable, [Product] = CurrentProduct),
                        [TotalDiff]
                    )
                ),
                ProductTotal
            ),
            SUMX(SummaryTable, [TotalDiff]) // ⬅️ Subtotal logic
        )
Justas4478's avatar
Justas4478
Post Prodigy
1 year ago

mdaatifraza5556 I tired it but it still returns different results when you filter.
The value circled in red is what I am hoping would be always shown.


And I would hope it would be same result in all rows in that column

 

FBergamaschi's avatar
FBergamaschi
Super User
1 year ago

CALCULATE(

                SUM('Stock adjustment data'[Difference Quantity(PIC)]),

               REMOVEFILTERS('Calendar'[Date]),

               REMOVEFILTERS('Stock adjustment data')
)

in this eay you remove all the filters, let's see if that is OK or you need to preserve some and we will fix it

 

If this helped, please consider giving kudos and mark as a solution

me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

  • Justas4478's avatar
    Justas4478
    Post Prodigy
    1 year ago

    FBergamaschi This is solution provided by copilot. It seams to work.
    I dont know if there is need to siplify it but here it is.

    VAR SummaryTable =
        CALCULATETABLE(
            SUMMARIZE(
                FILTER(
                    'Stock adjustment data',
                    'Stock adjustment data'[Physical Inventory Status] = "POST"
                ),
                'Product information'[Product],
                "TotalDiff", SUM('Stock adjustment data'[Difference Quantity(PIC)])
            ),
            REMOVEFILTERS('Calendar'[Date]), // ⬅️ This removes the impact of the date slicer
            ALL('Stock adjustment data')
        )

    VAR CurrentProduct =
        SELECTEDVALUE('Product information'[Product])

    VAR ProductTotal =
        CALCULATE(
            MAXX(
                FILTER(SummaryTable, [Product] = CurrentProduct),
                [TotalDiff]
            )
        )

    RETURN
        IF(
            ISINSCOPE('Product information'[Product]),
            IF(
                ProductTotal < 0,
                CALCULATE(
                    MINX(
                        FILTER(SummaryTable, [Product] = CurrentProduct),
                        [TotalDiff]
                    )
                ),
                ProductTotal
            ),
            SUMX(SummaryTable, [TotalDiff]) // ⬅️ Subtotal logic
        )