Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Measure gives wrong numbers

Hi guys,

 

I am trying to make some sort of planning: The hours worked and the planned hours should be combined into one running total. For the most part it works just fine, until I get to the cumulative total. (and the total of Test_Total).

 

 

 

 

 

 

 

 

 

 

 

 

Maandnummer: number of the different months.

Hours: Hours that were planned in advance.

Quantity: Actually worked Hours.

 

Test_Total: a measure to decide wether I need Hours or Quantity (based on current month)

Test_Total = IF(DATEDIFF(MAX(TimeTransactions[Date]), NOW(), DAY) > 0, SUM(TimeTransactions[Quantity]), IF(SUM(Forecast[Hours]) = BLANK(), 0, SUM(Forecast[Hours])))

 

Test_FinalTotal =  The cumulative total of Test_Total

Test_FinalTotal = CALCULATE(Test[Test_Total],FILTER(ALL(PowerBI_Calendar[Maandnummer]), PowerBI_Calendar[Maandnummer] <= MAX(PowerBI_Calendar[Maandnummer])))
 
Does anyone know what is going wrong?

 

Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    hi Anonymous 

    Just adjust the formula as below:

    Test_FinalTotal 3 = SUMX(FILTER(ALL(PowerBI_Calendar[Maandnummer]), PowerBI_Calendar[Maandnummer] <= MAX(PowerBI_Calendar[Maandnummer])),[Test_Total])

     

    or

    Test_FinalTotal 2 = SUMX(FILTER(ALL(PowerBI_Calendar), PowerBI_Calendar[Date] <= MAX(PowerBI_Calendar[Date])),[Test_Total])

    Result:

     

     

    Regards,

    Lin

3 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    6 years ago

    hi Anonymous 

    Just adjust the formula as below:

    Test_FinalTotal 3 = SUMX(FILTER(ALL(PowerBI_Calendar[Maandnummer]), PowerBI_Calendar[Maandnummer] <= MAX(PowerBI_Calendar[Maandnummer])),[Test_Total])

     

    or

    Test_FinalTotal 2 = SUMX(FILTER(ALL(PowerBI_Calendar), PowerBI_Calendar[Date] <= MAX(PowerBI_Calendar[Date])),[Test_Total])

    Result:

     

     

    Regards,

    Lin

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous , what is wrong and what is expected output

    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Hi Anonymous 

    I see your problem.

    it looks like you have too much tables here 🙂

    check relationships between PowerBI_Calendar and Forecast tables as problem appears when you need to sum up Forecast[Hours]