Forum Discussion

Kenny123's avatar
Kenny123
Frequent Visitor
3 years ago
Solved

DAX Aggregation Total Challenge

Hello Guys,

 

I have a white hair challenge that I've been trying to solve for at least a week without any success. The requirement is fairly simple, basically trying to produce the correct totals based on amounts that are rolled up to the parent .

 

Screenshot shown below attempts to highlight the totals issue and also how the amounts get rolled up in the detail rows.

 

There is a single filter on FY = 2019. When rolled up, the child projects shows 0 as amount which is expected as the dollars are attributed at the parent level.

 

I'm really hoping someone can provide some guidance around how to solve this one as it's driving me and my colleage up the wall 😞

 

Here is the DAX code currently used for the totals:

 

 

SUMX(
                    VALUES('Table'[ProjectID]), 
                    CALCULATE([Total Amount by Project],  'Table'[AllocationID] IN {"_Not Applicable", ""})
                )

                +

                SUMX(
                    VALUES('Table'[AllocationID]),
                    CALCULATE([Total Amount by Project],  NOT('Table'[AllocationID] IN {"_Not Applicable", ""}))
                )

 

 

Also attached the PIBX file used to demo the issue.

 

Sample-Aggregation.Totals.Challenge.pbix 

 

Any assistance would be greatly appreciated 🙂

Need Help
  • BrianConnelly's avatar
    BrianConnelly
    3 years ago

    Modify your code with the following...

    VAR ProjectID = SELECTEDVALUE('Table'[ProjectID],"ALL")
VAR sumTotal = IF(ProjectID = "ALL", 
CALCULATE(sum('Table'[Amount]),FILTER(ALLEXCEPT('Table','Table'[FY]),'Table'[AllocationID] IN VALUES('Table'[ProjectID])))
, CALCULATE(sum('Table'[Amount]),FILTER(ALLEXCEPT('Table','Table'[FY]),'Table'[AllocationID] = ProjectID))
)

Return IF(ISINSCOPE('Table'[ProjectID]),Decision,sumTotal)

     

    Your full code...

    Total Amount (Aggregated) = 

	VAR Result = 

        IF(MAX('Table'[AllocationID]) IN {"_Not Applicable", ""},

            [Total Amount by Project],

            IF(LEFT(MAX('Table'[ProjectID])) =  "A",

                // Aggreagate for Allocations Only
                
                CALCULATE(
                    SUM('Table'[Amount]),
                    FILTER(
                        ALLEXCEPT('Table', 'Table'[FY]),
                        'Table'[AllocationID] = MAX('Table'[AllocationID]) 
                    )
                )
                ,
                
                // Child Projects return nothing as they already rolled up to Allocation
                0
                
            )
        )


	
	VAR Decision = 
        IF(HASONEFILTER('Table'[ProjectID]),

            // Detailed Rows
                Result,


            // Totals Row
            
               SUMX(
                    VALUES('Table'[ProjectID]), 
                    CALCULATE([Total Amount by Project],  'Table'[AllocationID] IN {"_Not Applicable", ""})
                )

                +

                SUMX(
                    VALUES('Table'[AllocationID]),
                    CALCULATE([Total Amount by Project],  NOT('Table'[AllocationID] IN {"_Not Applicable", ""}))
                )  
        )
VAR ProjectID = SELECTEDVALUE('Table'[ProjectID],"ALL")
VAR sumTotal = IF(ProjectID = "ALL", 
CALCULATE(sum('Table'[Amount]),FILTER(ALLEXCEPT('Table','Table'[FY]),'Table'[AllocationID] IN VALUES('Table'[ProjectID])))
, CALCULATE(sum('Table'[Amount]),FILTER(ALLEXCEPT('Table','Table'[FY]),'Table'[AllocationID] = ProjectID))
)

Return IF(ISINSCOPE('Table'[ProjectID]),Decision,sumTotal)

10 Replies

  • BrianConnelly's avatar
    BrianConnelly
    Resolver III
    3 years ago

    Use the ISINSCOPE....

    Return IF(ISINSCOPE('Table'[ProjectID]),Decision,CALCULATE(sum('Table'[Amount])))

     

    Your Measure...

    Total Amount (Aggregated) = 

	VAR Result = 

        IF(MAX('Table'[AllocationID]) IN {"_Not Applicable", ""},

            [Total Amount by Project],

            IF(LEFT(MAX('Table'[ProjectID])) =  "A",

                // Aggreagate for Allocations Only
                
                CALCULATE(
                    SUM('Table'[Amount]),
                    FILTER(
                        ALLEXCEPT('Table', 'Table'[FY]),
                        'Table'[AllocationID] = MAX('Table'[AllocationID]) 
                    )
                )
                ,
                
                // Child Projects return nothing as they already rolled up to Allocation
                0
                
            )
        )


	
	VAR Decision = 
        IF(HASONEFILTER('Table'[ProjectID]),

            // Detailed Rows
                Result,


            // Totals Row
            
               SUMX(
                    VALUES('Table'[ProjectID]), 
                    CALCULATE([Total Amount by Project],  'Table'[AllocationID] IN {"_Not Applicable", ""})
                )

                +

                SUMX(
                    VALUES('Table'[AllocationID]),
                    CALCULATE([Total Amount by Project],  NOT('Table'[AllocationID] IN {"_Not Applicable", ""}))
                )  
        )

	Return IF(ISINSCOPE('Table'[ProjectID]),Decision,CALCULATE(sum('Table'[Amount])))
    • Kenny123's avatar
      Kenny123
      Frequent Visitor
      3 years ago

      Thank you heaps, was so excited until it return a dud when I filtered on ProjectID. Is there a way to produce the correct total if filter is applied on FY or ProjectID?




       

      • BrianConnelly's avatar
        BrianConnelly
        Resolver III
        3 years ago

        Im not sure what value you are expecting here.... is this the result you expect?

        Return IF(ISINSCOPE('Table'[ProjectID]),Decision,CALCULATE(sum('Table'[Amount]),ALL('Table'[FY],'Table'[ProjectID])))