Total shows blank with calculated measure

[Anonymous]

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.

Return Profit Share to KH =

IF( SELECTEDVALUE( Shipment[CompanyCode],BLANK()) = "JWDA",

BLANK(),

CALCULATE(SUMX(Shipment,

(Shipment[ProfitSharePercent]/100) * ([Gross Profit])),

FILTER( ALL(Shipment),

Shipment[Master Job No] = MAX(Shipment[Master Job No])),

FILTER( ALL( 'Company Master'),

'Company Master'[Company ID] <> "JWDAC"),

FILTER(ALL(Shipment),

Shipment[ProfitShareTo] = "CAMBODIA")))

 Moreover, I have tried to use IF( HASONEVALUE()) but it appears to be something wrong when I use the outer filter, for instance, the slicer with the time periods.

[v-shex-msft]

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

[Anonymous]

Dear @v-shex-msft @amitchandak @nandukrishnavs 

Please find the following pbix file for your information Dummy BI

Thank you in advance. 

[nandukrishnavs · Accepted solution]

@Anonymous 

Below are my findings.

• MAX ( Shipment[Master Job No] ) is JTH-2005-0072
• If you verify the Gross Profit of JTH-2005-0072, You can find it is blank.

What is the actual logic for identifying the MAX of [Master Job No]?

Just for testing purposes I have modified your logic like below.

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

[Anonymous]

Dear @nandukrishnavs 

I would like to filter the jobs that have the same MasterJobNo. Anyway your post figures me out.

Thank you & Best regards,

[amitchandak]

@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

https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/500115

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/

[Anonymous]

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.

Output from the calculated measure

May I know how to attach pbix to this post? 

Best Regards,

[nandukrishnavs]

@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