Problems with Total

[Eriha591]

Hi,

I am currently trying to build a report for a competition between different business units.
Every month we want to crown "Business of the month" based on 6 KPI:s and at the end of the year a total winner.

Some months are more important than others as shown in the formula below. Month 5,6 and 11 have a factor of 2 and 12 has a factor of 4. The other months have a factor of 1.

I have six of the formulas below and one measure that add them and multiplies with is weight (4 are worth 0,2 each and 2 are worth 0,1).

Rank (for one of the 6 KPIs) = IF([Sales mtd]=0,0,RANKX(allselected(DimStore[key_name]),[Share of ecommerce MTD],,ASC))*IF(value(left(RIGHT(format(LASTDATE('Date'[Datum2]),"YYYY-MM-DD"),5),2))=5,2,IF(value(left(RIGHT(format(LASTDATE('Date'[Datum2]),"YYYY-MM-DD"),5),2))=6,2,IF(value(left(RIGHT(format(LASTDATE('Date'[Datum2]),"YYYY-MM-DD"),5),2))=11,2,IF(value(left(RIGHT(format(LASTDATE('Date'[Datum2]),"YYYY-MM-DD"),5),2))=12,4,1))))

Total points = ([Rank KPI 1]*0.2)+([Rank KPI 2*0.2)+([Rank KPI 3]*0.2)+([Rank KPI 4]*0.2)+([Rank KPI 5]*0.1)+([Rank KPI 6]*0.1)

All these calculations works month by month but I cannot compute the total in the correct way. I would like to have 13 columns - one for each month and a Total. How do I accomplish that?
I would like to have formula that adds all the months together to a total.

Thanks in advance!

[Bernard Heymans]

Dear Eriha,

I didn't read your DAX... but
Normaly you organise your data , then you link a calendar (I prefer to create my own calendar) , you make the link between them (based on the date, which is unique in calendar)

The KPI are then an easy formula...

the pivot show the result for each period shown.

RANKX is a grood function ... but it will never have performance. You are beter to create agragated table in advance. Otherway each refresh of the DAX will recalculate all this... and will be very slow.