Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
salonikhedkar_1
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
v-lili6-msft
Community Support
Community Support

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.
amitchandak
Super User
Super User

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

@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

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.
VijayP
Super User
Super User

@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




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.