Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |