Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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 ;). |
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. | Proud to be a Super User! |
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.
I have changed the DAX function as you have suggested and wrong result as the _PowerBICommunity columns
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.
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.
Hi Michael,
It works now, thank you very much for your quick solution!
Peter Nguyen
Awesome! Thank you for your feedback!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
138 | |
107 | |
104 | |
82 | |
63 |