Forum Discussion
IF Formula sum
Hi JConnol4,
Kindly share your sample data and your formulas if the sample doesn't meet your requirement.
13 Period Indicent = CALCULATE(SUM(Table1[incidents]),ALLEXCEPT(Table1,Table1[id]))
hot spot = IF([13 Period Indicent]>11,1,0)
Measure = CALCULATE(DISTINCTCOUNT(Table1[id]),FILTER(Table1,[hot spot]=1))
For more details, please check the pbix as attached.
Regards,
Frank
Hi Frank,
Thanks very much for the email mate. I have tried to do this but i have not succeeded. The issue maybe due to me not having any of these included within the base data table. At present they are all measures and my base data is not any sort of pivot it is merely just rows of incident data. Unfortunately i can't share it on here for you to see but it is date of incident - location - inc type - count.
My formulas/processes at present are as such:
1) Sum my incidents in a 13 periodbasis (1 year) by using a measure
13 Period Indicent = CALCULATE(SUM('TRUST and SMIS'[Count]),DATESINPERIOD('TRUST and SMIS'[Incident Date],Date(2018,08,19),-365,day))
2) Next i calculate if this is over 11 incidents
hot spot = IF([13 Period Indicent]>11,1,0)
it is here that the tables are not recognising the information - should i add this to the base data or create a new table on this?
I will try again today so any help is appreciated
once again thanks
james
Hi JConnol4,
We need create a dimtime table in advance.
dimtime = CALENDARAUTO()
In your formula [13 Period Indicent], that should be a static value. If you want to get the total sum of previous year, you should update the formula, Please check the new one.
13 Period Indicent new = CALCULATE(SUM('TRUST and SMIS'[Count]),DATESINPERIOD(dimtime[Date],MAX('TRUST and SMIS'[Incident Date]),-365,day))Then we can create the measures as below.
hot spot = IF([13 Period Indicent new]>11,1,0)
Measure = SUMX('TRUST and SMIS',[hot spot])For more details, please check the pbix as attached.
Regards,
Frank