DAX measure (add new columns) not showing total in table

[Anonymous]

Hi Experts,

I use DAX to add two new columns "NetWeight_Shipped" and "NetWeight_notShipped" based on existing column "NetWeight_Total" and groupped by "BatchNo" and filtered by "OutStockTransactionID" as below:

--New column

 NetWeight_Shipped = 
                        CALCULATE(
                            SUM(Fact_ShippingKPI[NetWeight_Total])
                            ,ALLEXCEPT(Fact_ShippingKPI,Fact_ShippingKPI[BatchNo])
                            ,Fact_ShippingKPI[OutStockTransactionID] <> 0
                        )

--New column

NetWeight_notShipped = 
                CALCULATE(
                    SUM(Fact_ShippingKPI[NetWeight_Total])
                    ,ALLEXCEPT(Fact_ShippingKPI,Fact_ShippingKPI[BatchNo])
                    ,Fact_ShippingKPI[OutStockTransactionID] = 0
                ) 

Then put those columns on table as the screenshot. However, two new columns not showing total values in table.

I have tried to apply SUM function for the new columns then wrong results for both individual and total values.

[KNP]

Possibly a data model issue.

Can we get a screenshot of the model showing all of the relationships please?

[Mikelytics]

Hi @Anonymous 

Please try

 NetWeight_Shipped = 

SUMX(
   VALUES(Fact_ShippingKPI[BatchNo]),
      CALCULATE(
         SUM(Fact_ShippingKPI[NetWeight_Total])
         ,ALLEXCEPT(Fact_ShippingKPI,Fact_ShippingKPI[BatchNo])
         ,Fact_ShippingKPI[OutStockTransactionID] <> 0
      )
)

and

NetWeight_notShipped = 
SUMX(
   VALUES(Fact_ShippingKPI[BatchNo]),
   CALCULATE(
      SUM(Fact_ShippingKPI[NetWeight_Total])
      ,ALLEXCEPT(Fact_ShippingKPI,Fact_ShippingKPI[BatchNo])
      ,Fact_ShippingKPI[OutStockTransactionID] = 0
   ) 
)

Best regards

Michael

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!

@ me in replies or I'll lose your thread.

[Anonymous]

I have changed the DAX function as you have suggested and wrong result as the _PowerBICommunity columns

[Mikelytics]

If this does not work please try

--New column

 NetWeight_Shipped = 
                        CALCULATE(
                            SUM(Fact_ShippingKPI[NetWeight_Total])
                            ,Fact_ShippingKPI[OutStockTransactionID] <> 0
                        )

--New column

NetWeight_notShipped = 
                CALCULATE(
                    SUM(Fact_ShippingKPI[NetWeight_Total])
                    ,Fact_ShippingKPI[OutStockTransactionID] = 0
                ) 

Best regards

Michael

-----------------------------------------------------

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!

@ me in replies or I'll lose your thread.

[Anonymous]

Still incorrect, sorry. My purpose is to group by BatchNo as well.

[Mikelytics · Accepted solution]

This is weird, ok another try 😄

 NetWeight_Shipped = 

SUMX(
   VALUES(Fact_ShippingKPI[BatchNo]),
      CALCULATE(
         SUM(Fact_ShippingKPI[NetWeight_Total])
         ,Fact_ShippingKPI[OutStockTransactionID] <> 0
      )
)

and

 NetWeight_Shipped = 

SUMX(
   VALUES(Fact_ShippingKPI[BatchNo]),
      CALCULATE(
         SUM(Fact_ShippingKPI[NetWeight_Total])
         ,Fact_ShippingKPI[OutStockTransactionID] <> 0
      )
)

Best regards

Michael

-----------------------------------------------------

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!

@ me in replies or I'll lose your thread.

[Anonymous]

Hi Michael,

It works now, thank you very much for your quick solution!

Peter Nguyen

[Mikelytics]

Awesome! Thank you for your feedback!