cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Total shows blank with calculated measure

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.
 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion
Community Champion

Re: Total shows blank with calculated measure

@Paniti 

 

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")))

 

Capture.JPG


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/109...

View solution in original post

7 REPLIES 7
Highlighted
Super User IV
Super User IV

Re: Total shows blank with calculated measure

@Paniti , 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/50...

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/

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Frequent Visitor

Re: Total shows blank with calculated measure

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 measureOutput from the calculated measure

May I know how to attach pbix to this post? 

 

Best Regards,

Highlighted
Community Champion
Community Champion

Re: Total shows blank with calculated measure

@Paniti 

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/109...

Highlighted
Community Support
Community Support

Re: Total shows blank with calculated measure

HI @Paniti,

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

Community Support Team _ Xiaoxin
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Frequent Visitor

Re: Total shows blank with calculated measure

Dear @v-shex-msft @amitchandak @nandukrishnavs 

 

Please find the following pbix file for your information Dummy BI

 

Thank you in advance. 

Highlighted
Community Champion
Community Champion

Re: Total shows blank with calculated measure

@Paniti 

 

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")))

 

Capture.JPG


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/109...

View solution in original post

Highlighted
Frequent Visitor

Re: Total shows blank with calculated measure

Dear @nandukrishnavs 

 

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

 

Thank you & Best regards,

Helpful resources

Announcements
June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors