Forum Discussion

brockry1's avatar
brockry1
Icon for Helper II rankHelper II
1 year ago
Solved

DAX Code to Sum a Measure by row vs total

I am building a new dashboard for my team and I have hit a few hurdles in the DAX commands.  I am looking to be able to show a KPI Card to sum up the individual rows compared to the growth %.  Currently it is using the logic to only show the growth % total.  

 

This is the formula I am currently using:

Rebate Earned = IF([Current Year]>=1250,

IF([Rebate %]>0,

CALCULATE([Rebate %]*[Current Year])))
 
It looks correct in the table version:  
 

 

 

 

However this is what is showing in the KPI Card:

 

 

Is there a better way of writing the DAX code to be able to sum this vs comparing to the total growth %? 

 

  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi brockry1 ,

    According to your description, you want to show the sum of Earned in the card view, right? We can use sumx instead of calculate, sometimes calculate affects the normal aggregation.

     

    Rebate Earned = 
SUMX(
    'Table',
    IF(
        [Current Year] >= 1250 && [Rebate %] > 0,
        [Rebate %] * [Current Year],
        0
    )
)

     

    Hope it helps!

    Best regards,
    Community Support Team_ Tom Shen

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

     

     

2 Replies

  • brockry1's avatar
    brockry1
    Icon for Helper II rankHelper II
    1 year ago

    I am building a new dashboard for my team and I have hit a few hurdles in the DAX commands.  I am looking to be able to show a KPI Card to sum up the individual rows compared to the growth %.  Currently it is using the logic to only show the growth % total.  

     

    This is the formula I am currently using:

    Rebate Earned = IF([Current Year]>=1250,

    IF([Rebate %]>0,

    CALCULATE([Rebate %]*[Current Year])))
     
    It looks correct in the table version:  
     

     

     

    However this is what is showing in the KPI Card:

     

    Is there a better way of writing the DAX code to be able to sum this vs comparing to the total growth %? 

     

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi brockry1 ,

    According to your description, you want to show the sum of Earned in the card view, right? We can use sumx instead of calculate, sometimes calculate affects the normal aggregation.

     

    Rebate Earned = 
SUMX(
    'Table',
    IF(
        [Current Year] >= 1250 && [Rebate %] > 0,
        [Rebate %] * [Current Year],
        0
    )
)

     

    Hope it helps!

    Best regards,
    Community Support Team_ Tom Shen

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.