Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
peternznguyen
Helper I
Helper I

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

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.

PowerBITotalValuemissing.png

1 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.

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

View solution in original post

8 REPLIES 8
KNP
Super User
Super User

Possibly a data model issue.

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

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x
Mikelytics
Resident Rockstar
Resident Rockstar

Hi @peternznguyen 

 

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.

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

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

peternznguyen_0-1667460963322.png

 

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.

 

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

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

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.

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

Hi Michael,

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

Peter Nguyen

Awesome! Thank you for your feedback!

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.