Forum Discussion
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:
Why does Power BI calculate in Octuber rate of 14,41 % ? How can I change this?
Thank you for your time.
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.
8 Replies
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.
Hi Anonymous
Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.
Best Regards,
Rico Zhou
Anonymous
First, let's format your measure to read it more easily:
Rolling Average 6 Months =
VAR result =
CALCULATE (
( DIVIDE ( 'Parts per Month'[DoAs]; [Count of Parts] ) ) * 100 + 0;
DATESINPERIOD ( Dates[Date]; MAX ( Dates[Date] ); -6; MONTH )
)
RETURN
IF (
result;
result;
IF ( MAX ( Dates[Date2] ) > DATE ( 2019; 9; 30 ); 0; BLANK () )
)Here I don't understand why you have the first IF, so believe it can be simplified to the below, with no difference in result:
Rolling Average 6 Months =
VAR result =
CALCULATE (
( DIVIDE ( 'Parts per Month'[DoAs]; [Count of Parts] ) ) * 100 + 0;
DATESINPERIOD ( Dates[Date]; MAX ( Dates[Date] ); -6; MONTH )
)
RETURNresult
You never make it to this part of the formula because you don't actually have a true false statement in your first IF statement:
IF ( MAX ( Dates[Date2] ) > DATE ( 2019; 9; 30 ); 0; BLANK () )For your question, I suspect this may be related to the fact that October is the first month, can you please also share what calculation you are using for [Count of Parts] and please tell us what is Dates[Date2] and why do you need to compare to 2019/9/30?
Anonymous , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
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- 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.
