Forum Discussion

jcarville's avatar
jcarville
Skilled Sharer
8 years ago
Solved

Incorrect Measure Total

Hi,   I am getting the incorrect total for my measure '% Turnover Value'. The total is showing 135,656, but I expect a figure of 69,089. I am presuming the Accounts with no 'Average of % Turnover'...
Need Help
Show and Tell
Tutorial Requests
  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    Hi,

     

    The SUMX() portion of the formula should be something like this

     

    =SUMX(SUMMARIZE(VALUES(Rebates[Account/Group]),[Account/Group],"ABCD",SUM(Sales value final)*AVERAGE(Rebates[% turnover])),[ABCD])

     

    Include the curency symbol in the underlines portion.

     

    If this formula does not help, then share the link from where i can download your PBI file.

Ashish_Mathur's avatar
Ashish_Mathur
Super User
8 years ago

Hi,

 

The SUMX() portion of the formula should be something like this

 

=SUMX(SUMMARIZE(VALUES(Rebates[Account/Group]),[Account/Group],"ABCD",SUM(Sales value final)*AVERAGE(Rebates[% turnover])),[ABCD])

 

Include the curency symbol in the underlines portion.

 

If this formula does not help, then share the link from where i can download your PBI file.

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Im at a breaking point, tried all the suggestions from many articles but still struggling with fixing totals.

1. created a measure to return sales last year using the below

VAR = CALCULATE(SUMX(Sales,Sales[revenue local currency]),DATEADD('Calendar'[Date],-1,YEAR))
 
2. Created another measure to return fx rate from the sames for the selected year and month on the slicer
 
VAR B =MIN(Sales[rate])
 
3. convert the local currency revenue to USD
DIVIDE(A,B,0)

Calculation works, but the total is incorrect. I used the below DAX in one measure,
 
REV_USD_LY = 
VAR A = CALCULATE(SUMX(Sales,Sales[revenue local currency]),DATEADD('Calendar'[Date],-1,YEAR)) 
VAR B = CALCULATE(MIN(Sales[rate]),ALL('Calendar'))
VAR div = DIVIDE(A,B,0)
 
If you see the total, it returns sames as revenue local currency last year, can you please help?