Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
If you still have the problem, please share your sample pbix file for us have a test.
Regards,
Lin
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.
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
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
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
Proud to be a Super User!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |