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.
Hello,
I am struggling with my measure. The measure works properly with each row. But the total row in the table shows nothing (BLANK()). The syntax of my measure is attached below.
Solved! Go to Solution.
@Anonymous
Below are my findings.
Return Profit Share to KH =
IF( SELECTEDVALUE( Shipment[CompanyCode],BLANK()) = "JWDA",
// This measure will return BLANK() when JWD TH's jobs are calculated
BLANK(),
CALCULATE(SUMX(Shipment,
(Shipment[ProfitSharePercent]/100) * ([Gross Profit])),
// Match the related master job numbers
FILTER( ALL(Shipment),
Shipment[Master Job No] IN DISTINCT('Job Order'[MasterJobNo])),
// Retreive the profit that share from JWD TH only
FILTER( ALL( 'Company Master'),
'Company Master'[Company ID] <> "JWDAC"),
FILTER(ALL(Shipment),
Shipment[ProfitShareTo] = "CAMBODIA")))
Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂
Here is my latest blog
https://community.powerbi.com/t5/Community-Blog/Dynamic-Page-Navigation-Based-on-User-Login/ba-p/1097786
HI @Anonymous,
You can try to use the following measure formula if it suitable for your requirement:
Return Profit Share to KH =
VAR CompanyList =
CALCULATETABLE (
VALUES ( 'Company Master'[Company ID] ),
FILTER ( ALL ( 'Company Master' ), 'Company Master'[Company ID] <> "JWDAC" )
)
VAR currJob =
MAX ( Shipment[Master Job No] )
RETURN
IF (
ISFILTERED ( Shipment[Master Job No] ),
"detail level",
IF (
NOT ( "JWDA" IN VALUES ( Shipment[CompanyCode] ) ),
CALCULATE (
SUMX ( Shipment, Shipment[ProfitSharePercent] / 100 * [Gross Profit] ),
FILTER (
ALL ( Shipment ),
Shipment[Master Job No] = currJob
&& Shipment[ProfitShareTo] = "CAMBODIA"
&& Shipment[CompanyCode] IN CompanyList
)
)
)
)
If above not help, please share some dummy data with the same structure to test:
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
Dear @v-shex-msft @amitchandak @nandukrishnavs
Please find the following pbix file for your information Dummy BI
Thank you in advance.
@Anonymous
Below are my findings.
Return Profit Share to KH =
IF( SELECTEDVALUE( Shipment[CompanyCode],BLANK()) = "JWDA",
// This measure will return BLANK() when JWD TH's jobs are calculated
BLANK(),
CALCULATE(SUMX(Shipment,
(Shipment[ProfitSharePercent]/100) * ([Gross Profit])),
// Match the related master job numbers
FILTER( ALL(Shipment),
Shipment[Master Job No] IN DISTINCT('Job Order'[MasterJobNo])),
// Retreive the profit that share from JWD TH only
FILTER( ALL( 'Company Master'),
'Company Master'[Company ID] <> "JWDAC"),
FILTER(ALL(Shipment),
Shipment[ProfitShareTo] = "CAMBODIA")))
Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂
Here is my latest blog
https://community.powerbi.com/t5/Community-Blog/Dynamic-Page-Navigation-Based-on-User-Login/ba-p/1097786
Dear @nandukrishnavs
I would like to filter the jobs that have the same MasterJobNo. Anyway your post figures me out.
Thank you & Best regards,
@Anonymous , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
This example change calc based on selected value
refer this doc how to hasonevalue
https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/
isinscope
https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/
Dear @amitchandak
The output of my measure is in the picture below. I would like the measure "Return Profit Share to KH" shows grand total on the bottom of table.
May I know how to attach pbix to this post?
Best Regards,
@Anonymous
Try this
Return Profit Share to KH =
VAR maxVal =
CALCULATE (
MAX ( Shipment[Master Job No] ),
ALL ( Shipment )
)
VAR result =
CALCULATE (
SUMX (
Shipment,
( Shipment[ProfitSharePercent] / 100 ) * ( [Gross Profit] )
),
FILTER (
ALL ( Shipment ),
Shipment[Master Job No] = maxVal
),
FILTER (
ALL ( 'Company Master' ),
'Company Master'[Company ID] <> "JWDAC"
),
FILTER (
ALL ( Shipment ),
Shipment[ProfitShareTo] = "CAMBODIA"
)
)
RETURN
IF (
SELECTEDVALUE (
Shipment[CompanyCode],
BLANK ()
) <> "JWDA",
result
)
Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂
Here is my latest blog
https://community.powerbi.com/t5/Community-Blog/Dynamic-Page-Navigation-Based-on-User-Login/ba-p/1097786
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 |
---|---|
114 | |
100 | |
88 | |
69 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |