ALL function with text

[Justas4478]

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 it tries to run.

This is what I was hoping would work.

CALCULATE(SUM('Stock adjustment data'[Difference Quantity(PIC)]),ALL('Calendar'[Date]),'Stock adjustment data'[Physical Inventory Procedure],'Stock adjustment data'[Reason])
The [Physical Inventory Procedure] and [Reason] are columns that I tried to add and they are rejected since they are text.
Is there anything that can be changed to make it work?

[v-sgandrathi]

Hi @Justas4478,

Great to hear it’s working well for you!

Your approach of using SUMMARIZE with a filter for "POST" status and grouping by product is effective. This ensures you get accurate totals per product, even with visual filters applied. Including logic to check for negative values is also a good move.

summarizing per product number is a good approach if you're aiming to get a consistent result per product. It helps avoid row-level filter effects and gives you better control over how totals are displayed.

Thank you again, and we hope you continue to find the Microsoft Fabric Community helpful!

[grazitti_sapna]

Hi @Justas4478 

Please try this:

ALCULATE(
SUM('Stock adjustment data'[Difference Quantity(PIC)]),
ALL('Calendar'[Date]),
ALL('Stock adjustment data'[Physical Inventory Procedure]),
ALL('Stock adjustment data'[Reason])
)

I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[mdaatifraza5556]

Hi @Justas4478 

Can you please try the belwo dax ?

--- > All( ) does not support multiple columns from different tables.

CALCULATE(
SUM('Stock adjustment data'[Difference Quantity(PIC)]),
ALL('Calendar'[Date]),
ALL('Stock adjustment data'[Physical Inventory Procedure]),
ALL('Stock adjustment data'[Reason])
)

If this answers your questions, kindly accept it as a solution and give kudos.

[FBergamaschi]

You need to specify the codition you want those column to fulfill, for example:

CALCULATE(

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

               ALL('Calendar'[Date]),

               'Stock adjustment data'[Physical Inventory Procedure] ="Value1",
               'Stock adjustment data'[Reason]="Value2"
)

or some other criteria (pls clarify so I can help)

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]

@FBergamaschi These are all the values in [Physical Incentory Procedure]:
AL
AS
HL
HS
And in [Reason]:
CCIV
LSPI
PTPI
STND
UNAS
UPLD

Do I have to list them all in the dax to make sure that it works?

[FBergamaschi]

That depends on what you want to impose on those columns?

You want to allow only some values (which?) or you wanto to remove the filters form thos columns? Please explain and I shall provide the code

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

[mdaatifraza5556]

Hi @Justas4478 

No need to add all the values.

CALCULATE(
SUM('Stock adjustment data'[Difference Quantity(PIC)]),
ALL('Calendar'[Date]),
ALL('Stock adjustment data'[Physical Inventory Procedure]),
ALL('Stock adjustment data'[Reason])
)

If this answers your questions, kindly accept it as a solution and give kudos.

[Justas4478]

@mdaatifraza5556 I was thinking, maybe it needs to be summarized per product number and not sum to get outcom I am aiming for

[Justas4478]

@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]

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 · Accepted solution]

@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

    )