cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
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.

 

please help. Thank you in advance

5 REPLIES 5
Highlighted
Solution Sage
Solution Sage

Re: Formula & Matrix visualization

@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

2020-04-28_083026.png

Highlighted
Super User IV
Super User IV

Re: Formula & Matrix visualization

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Community Support
Community Support

Re: Formula & Matrix visualization

hi  @salonikhedkar_1 

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
Helper II

Re: Formula & Matrix visualization

@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

Highlighted
Community Support
Community Support

Re: Formula & Matrix visualization

hi  @salonikhedkar_1 

Sample data and expected output would help tremendously.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

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.

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

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