Forum Discussion

samc_26's avatar
samc_26
Icon for Helper IV rankHelper IV
1 year ago
Solved

SUMX help for total

Hi, I have some numbers that I have managed to calculate on a row level but they don't sum up properly for using on a card visual.

 

I think I need to use SUMX but I don't know how to implement it, I've tried with no success ☹️

 

I have a measure from one table which looks at potential sales based on how many hours a shop is open for (percentage) using two columns in one table as below: 

 

AreaMax potential salesOpening hoursActual potential sales
France10,00075%7,500
Spain50,00050%25,000
Germany20,00025%5,000

 

I then have a measure which mulitplies Max potential sales by the availability of opening hours which gives me this data below which works fine but when I come to the total at the bottom it's way too high. Can anyone tell me what I need to do please? 

 

This is what works on a row level but not for the total:

 

'Actual potential sales = sumx(Sales, Sales[Max potential sales])* SUMX(Sales, Sales[Opening hours])

 

AreaActual potential sales
France7,500
Spain25,000
Germany5,000
Total1,500,000*

 

*(i've made this number up but it's completely out)

 

Any help would be a massive help thank you!

 

Need Help
  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    1 year ago

    Hi samc_26 ,
    You are using SUMX separately for each column and then multiplying the results, which leads to an incorrect total calculation.

    This is updated measure:

    Actual potential sales = 
SUMX(
    Sales, 
    Sales[Max potential sales] * Sales[Opening hours]
)

2 Replies

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Icon for Super User rankSuper User
    1 year ago

    Hi samc_26 ,
    You are using SUMX separately for each column and then multiplying the results, which leads to an incorrect total calculation.

    This is updated measure:

    Actual potential sales = 
SUMX(
    Sales, 
    Sales[Max potential sales] * Sales[Opening hours]
)