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 = 

IF (MONTH (date) IN {1,2,3}, 23000/3,

IF (MONTH (date) IN {4,5,6}, 25000/3,
IF (MONTH (date) IN {7,8,9}, 25000/3,

IF (MONTH (date) IN {10,11,12}, 27000/3,0))))

 

This column is specified on Row level in Visualization - Matrix

 

2) Summing previous year amount and substituting $0 in place of blanks

A calculated measure formula:

PY = IF(ISBLANK(CALCULATE(SUM(amount)), SAMEPERIODLASTYEAR(date)),0,CALCULATE(SUM(amount)), SAMEPERIODLASTYEAR(date))))

 

This measure is placed in Values

 

-----------------------------------------------------------------------------------------------------------------------------------------------

However, the measure calculates extra $0 and makes the months repeat itself thrice, one with the actual amount, and others wuth $0

jan    23000    12000

jan    25000    0

jan    25000    0

feb    23000    12000

feb    25000    0

feb    25000    0

  

 

whereas it should show:

 

jan    23000    12000

feb    23000    6000

mar    23000    700

apr    25000    30000

 

and so on 

 

------------------------------------------------------------------------------------------------------------------------------------------

when i divide the target equally by 12, this issue does not arise.

 

please help. Thank you in advance

Need Help

5 Replies

  • VijayP's avatar
    VijayP
    Community Champion
    6 years ago

    salonikhedkar_1 

    I have got the answer with Single Cell. I think you are directlly dealing with Date avaiable in Fact table.

    I suggest to create the Date Dim and relate it to Fact Table and the result will be as below. Also attaching the PBIX File for your consumption. You Cand Download the PBIX File from this Link

     

    If you liked this solution please mention as solved and share your Kudos

    Also you can watch my Videos on Power BI and Excel  by clicking this Link

    Regards

    Vijay Perepa

  • 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