Forum Discussion

salonikhedkar_1's avatar
salonikhedkar_1
Helper II
6 years ago

Formula & Matrix visualization

Hi, The issue is stated in two parts, 1) Dividing a target on quarter basis   Suppose target for the whole year is 100,000, the calculated column divides it on quartery basis, formula: Target = ...
Need Help
amitchandak's avatar
amitchandak
Super User
6 years ago

salonikhedkar_1 

Your measure can simply be - CALCULATE(SUM(amount), SAMEPERIODLASTYEAR(date))+0

Also, create a table for your budget, if needed allocate it at the Budget at the month level.

 

Use a common Date dimension and use that to do you analysis.

  • salonikhedkar_1's avatar
    salonikhedkar_1
    Helper II
    6 years ago

    amitchandak 

    Yes I do have a target table. But it is not a date dimension. Its just a simple table of country, target, month and a concat field, concatenating country and month. Based on this concatenated field, I am joining this target table with my main table.

    This works well. But breaks in conditional formatting. I have a measure which supports conditional formatting. the formula is as follows:

    TARGET_MEASURE =
    var TARGET = SELECTCOLUMNS(TargetTable, "TARGET", TargetColumn)
    RETURN

    SWITCH(TRUE(),
    TARGET>= Amount,1,
    TARGET<= Amount,2,
    ISBLANK(Amount),3)

    ------------------------------------------------------------------------------------------
    This formula compared target vs amount, if target is higher than amount, then 1.....and so on. 
    It worked well when I had a Target column in my existing table. But when I made a separate Target table, it is consideing consition1 and Blanks as 1