Cumlative and non cumulative measure

[Bunta]

 Hi all,

I have a question over design of a measure table and another around the measures that sit within it in Power BI.

1. The data I receive comes in a long skinny file similar to figure 1. However the measures cannot, with the example being SFLU1 and SFLU12, be summed as their construction is different. Am I right to pivot the data to make each measure a column to stop this like in figure 2

In the example I have put together I have two different styles of measures from the same collection.

Scenario 1: Measure 1 is put into a table without the achievement_date column. This is fine as Measure 1 can be summed over time

Scenario 2: Measure 12 is put in a table WITH the achievement_date column. This is fine as measure 12 is a point in time measure

Scenario 3: Measure 12 is put in a table WITHOUT the achievement_date column. This can’t happen as Measure 12 cannot be summed over time due to double counting

1. Can I create one measure table for the above scenarios above or do I need one table for measure 1 and one for measure 12 or can a calculated measure be used to solve the issue?

CODE	ACHIEVEMENT_DATE	MEASURE	VALUE
1	30/09/2015	SFLU001	247
1	31/10/2015	SFLU001	1128
1	30/09/2015	SFLU012	61
1	31/10/2015	SFLU012	270
2	30/09/2015	SFLU001	128
2	31/10/2015	SFLU001	1168
2	30/09/2015	SFLU012	54
2	31/10/2015	SFLU012	245

Fig 1

CODE	ACHIEVEMENT_DATE	SFLU001	SFLU012
1	30/09/2015	247	61
1	31/10/2015	1128	270
2	30/09/2015	128	54
2	31/10/2015	1168	245

Fig 2

Thanks

[v-huizhn-msft]

Hi @Bunta,

>>The issue I'm trying to get to the bottom of now, is that of the measures in the file some of them can be summed up over time whilst some can't. For example measure 1 is ok to sum over time as it only counts people the first time in the first month they appear no matter how times they appear. Measure 12 though counts people in the month meaning that that they can appear in every month and so summing the measure will lead to double counting.

Based on your description, I am still confused your issue. And how to count people for Measure1 and Measure12, could you please share more datails and use number in table to list your requriement?  So I can help you directly.

Thanks,
Angelia

[Bunta]

Hi Angela,

Thanks very much for taking the time to reply. I have edited the post and hope it is easier to understand now. I don't know if Power BI can do it one table or not.

Thanks

[v-huizhn-msft]

 Hi @Bunta,

Yeah, Power BI can do it. I test it using your given sample data.(named 'Test1').



Then click New Table under Modeling on home page, type the DAX statement, you will get expected result, then right click the CODE column header->Sort Ascending(highlighted in red line), please review the screenshot below.

Table 3 = SUMMARIZE(Test1,Test1[CODE],Test1[ACHIEVEMENT_DATE],"SFLU001",CALCULATE(SUM(Test1[Value]),FILTER(Test1,Test1[MEASURE]="SFLU001")),"SFLU012",CALCULATE(SUM(Test1[Value]),FILTER(Test1,Test1[MEASURE]="SFLU012")))

If you have other issues, don't hesitate to let me know.

Best Regards,
Angelia

[Bunta]

Hi Angelia,

Thanks for the response. Sorry for the late reply but I've been away until today. I've input your solution and it pivots the data which solves one problem, however it still allows the measure SFLU012 to summed up when no achievement_date has been specified. I.E. if I create a table with just code and SFLU012 it sums up SFLU012 which is what can't happen.

I've created a calculated measure below and I assume there is no way round apart from specifing a measure that requires a achievement date to be specified by using a filter.

SFLU012_CALC = if(VALUES('Table 3'[ACHIEVEMENT_DATE]) = BLANK(), BLANK(), (sum('Table 3'[SFLU012])))

Is this the only way round to use something like the above in the code you provided?

thanks

[v-huizhn-msft]

Hi @Bunta,

Do you still list the achievement date in result table? Please try a calculated column using your the if formula.

if(VALUES('Table 3'[ACHIEVEMENT_DATE]) = BLANK(), BLANK(), 'Table 3'[SFLU012])

 Then use formula above to create new table.

Best Regards,
Angelia