Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Divide Calculation - 6 month average wrong but why?

Hey Power BI Community! Please help me I am frustraded... I just want the DoAs Divided by parts for example Okctober 2019 = (5 DoAs / 222 Parts)*100 = 2,25 % These are my measures: Roll...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous 

    I think you want to calculate the percent by divide count of per month ID and sum of quantity rolling 6 month. I build three tables to have a test.

    Table1:

    Table2:

    Build a calendar table and build relationships between Table1 and CalendarTable 's Date column.

     

    calendar = ADDCOLUMNS(CALENDARAUTO(),"Year",YEAR([Date]),"Month",MONTH([Date]))

     

     

    Build a measure to achieve your goal.

     

    Rolling Average 6 Months =
VAR Dos =
    CALCULATE (
        COUNT ( Table2[ID] ),
        FILTER (
            Table2,
            Table2[Year] = MAX ( 'calendar'[Year] )
                && Table2[Month] = MAX ( 'calendar'[Month] )
        )
    )
VAR Count_of_Parts =
    CALCULATE (
        SUM ( 'Table1'[Quantity] ),
        DATESINPERIOD ( 'calendar'[Date], FIRSTDATE ( 'calendar'[Date] ), 6, MONTH )
    )
RETURN
    DIVIDE ( Dos, Count_of_Parts )

     

    Result:

    You can download the pbix file from this link: Divide Calculation - 6 month average wrong but why

     

    Best Regards,

    Rico Zhou

     

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

amitchandak's avatar
amitchandak
Super User
6 years ago

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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Thank you for your time! My target is to show data from october 2019 - to today/future

Date2 = Dates[Date]
Dates = CALENDAR(DATE(2010;01;01);DATE(2025;12;31))
 
DoAs = var result =count('Stammdaten Komplett'[ID])
return if(result; result; IF(MAX(Dates[Date2])>DATE(2019;9;30);0; BLANK()))
 
Count of Parts = var result = SUM('All Parts'[adjusted quantity])
return if(result; result; IF(MAX(Dates[Date2])>DATE(2019;9;30);0; BLANK()))

adjusted quantity = if('All Parts'[Quantity]>10; 1; 'All Parts'[Quantity])

I am sorry but more data is not possible without leaking sensitiv data


  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    6 years ago
    Anonymous
    Thanks, that data helps a bit. Your current measure is not doing any filter on the date. You can either use a visual or page filter or date slicer:
    https://docs.microsoft.com/en-us/power-bi/visuals/desktop-slicer-filter-date-range#:~:text=You%20can%20use%20the%20relative%20date%20slicer%20just%20like%20any,corner%20of%20the%20slicer%20visual.

    Or if you want to do this in a measure, you could try:
    Count of Parts =
    SUMX( FILTER('All Parts', 'All Parts'[Month] > DATE(2019;9;30)), 'All Parts'[adjusted quantity])

    Depending on what other requirements you have, may need to add some additional tweaks to add/ignore other filters.

    Also, I don't understand why you need Date2? You should be able to just use Dates[Date] whenever that is needed.
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you for your answer!

       

      Rolling Average 6 Months = var result = calculate((DIVIDE('Parts per Month'[DoAs]; [Count of Parts]))+0; DATESINPERIOD(Dates[Date]; MAX(Dates[Date]); -6; month))
      return if(result; result; IF(MAX(Dates[Date2])>DATE(2019;9;30);0; BLANK()))

      I already have a filter on the date.