Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
Could someone please help me on the below.
i need to calculate the below measures as shown in screenshot.
i created the measures as below.
1.Actual YTD =
var val= TOTALYTD(SUMX('InfoMart CostOut_newtest','InfoMart CostOut_newtest'[Value]),'InfoMart CostOut_newtest'[DateKey])
RETURN
val
2.Target YTD=
var val= TOTALYTD(SUMX('InfoMart Costout_Target2021','InfoMart Costout_Target2021'[Target]),''InfoMart Costout_Target2021'[DateKey])
RETURN
val
3.FY target=
SUMX (
FILTER ( 'InfoMart Costout_Target2021', RELATED ( 'InfoMart Costout_Target2021'[DateKey] ) ),
'InfoMart Costout_Target2021'[Target]
could someone please let me know are these measures created are correct and if not could some one of you please correct them.
Regards
Anitham
Hi, @anithaMallam ;
Is your problem solved?? If so, Would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @anithaMallam ;
You could try it.
Actual YTD =
CALCULATE (
SUM ( 'InfoMart CostOut_newtest'[Value] ),
FILTER ( ALL ( 'InfoMart CostOut_newtest' ), [month] <= 6 )
)
Target YTD =
VAR val =
CALCULATE (
SUM ( 'InfoMart Costout_Target2021'[Target] ),
FILTER ( ALL ( 'InfoMart Costout_Target2021' ), [month] <= 6 )
)
RETURN
val
Target YTD =
VAR val =
CALCULATE (
SUM ( 'InfoMart Costout_Target2021'[Target] ),
ALL ( 'InfoMart Costout_Target2021' )
)
RETURN
val
Because it's not clear what your actual table data looks like? , so not sure if your formula is correct, and the [DateKey] in your expression?
Or based on the simple sample data you provide, provide the result you want to output.
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@anithaMallam Very hard to say given the information provided. Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, 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
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
I can say that I am not exactly a fan of TI functions. You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000
Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...
Hi Greg,
sample data which looks as bleow
Actual Table data
year | period | month | unit | value1 | value2 | total |
2021 | jan | 1 | A | 0 | 2.18 | 2.18 |
2021 | feb | 2 | B | 4.71117 | 17.24189 | 21.95306 |
2021 | march | 3 | C | 0.9 | 0 | 0.9 |
2021 | april | 4 | D | 0 | 0 | 0 |
Target Data Table
year | month | unit | ctype | value1 | value2 |
2021 | 1 | APC | SG&A | 0 | 0 |
2021 | 1 | ENN | SG&A | 0 | 0 |
2021 | 1 | BCP | SG&A | 0 | 0 |
Regards
Anitham
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
78 | |
65 | |
53 |
User | Count |
---|---|
144 | |
103 | |
98 | |
85 | |
64 |