CODEWITHSUNDEEP
powerbidax
hermg
hermg

Reputation: 3

SUM distinct values in DAX, Power BI

I am quite new to Power BI and now facing a problem, illustrated below.

Here

1

**DATA**
LEVEL | PROJECT NAME| BUDGET_TYPE | BUDGET_CODE | BUDGET_AMOUNT 
1       xxxx                A           0000001     4,800,000 
1       xxxx                A           0000002     4,300,000 
1       xxxx                A           0000002     900,000 
1                           A           0000003     1,300,000 
1                           A           0000004     4,780,000 
1                           A           0000010     3,900,000 
1                           A           0000010     3,900,000 
1                           A           0000011     200,000 
1                           A           0000015     1,028,165 
1                           A           0000015     1,028,165 
1                           B           0000016     83,000,000 
1                           B           0000017     83,000,000 
1                           B           0000017     28,200,000 
1                           B           0000018     15,000,000 
1                           B           0000019     4,800,000 
1                           B           0000020     7,000,000 
1                           B           0000020     7,000,000 

PIVOT TABLE from Excel
Row Labels  | Max of BUDGET_AMOUNT  
LEVEL: 1            83,000,000  
TYPE:A                  4,800,000   
    0000001     4,800,000   
    0000002     4,300,000   
    0000003     1,300,000   
    0000004     4,780,000   
    0000010     3,900,000   
    0000011     200,000     
    0000015     1,028,165    **20,308,165** 
TYPE:B                  83,000,000  
    0000016     83,000,000  
    0000017     83,000,000  
    0000018     15,000,000  
    0000019     4,800,000   
    0000020     7,000,000    **192,800,000** 
Grand Total         83,000,000

I have raw data and PIVOT TABLE, respectively. What I want is to find MAX amount of each CODE first. Then, SUM those MAX values only with distinct CODE.

I have attached the PIVOT TABLE for simplifying my problem. What I really want at the end is the SUM amount which is

LEVEL 1
TYPE A: 20,308,165
TYPE B: 192,800,000

Is there any way I can do that? Please help. Thanks!

Upvotes: 0

Views: 1424

Answers (1)

Marcus
Marcus

Reputation: 4015

You can do a measure that summarizes the different codes, calculates the max for each code, and sums up this table row-by-row:

Sum := 
VAR _tbl = 
    SUMMARIZE (
         'Table' , 
         'Table'[Level] , 
         'Table'[Budget Code] ,
         "Max Budget" , CALCULATE ( MAX ( 'Table'[Budget] ) )
    )
RETURN
    SUMX ( _tbl , [Max Budget] )

See minimal data example here:

enter image description here

Upvotes: 1

Related Questions