cancel
Showing results for
Did you mean:
Highlighted
Helper II

## 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.

5 REPLIES 5
Highlighted
Solution Sage

## Re: Formula & Matrix visualization

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

Highlighted
Super User IV

## Re: Formula & Matrix visualization

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.

Proud to be a Super User!

Highlighted
Community Support

## Re: Formula & Matrix visualization

If you still have the problem, please share your sample pbix file for us have a test.

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper II

## Re: Formula & Matrix visualization

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

Highlighted
Community Support

## Re: Formula & Matrix visualization

Sample data and expected output would help tremendously.

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

#### Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors