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!
I have a table as described under:
-----
Date | Location | KPI | Actual | Target
01.01.2018 | 1 | 1 | 0.94 | 1
01.02.2018 | 1 | 1 | 1.02 | 1
01.03.2018 | 1 | 1 | 0.78 | 1
...
01.01.2018 | 1 | 2 | 34812 | 30000
01.02.2018 | 1 | 2 | 38992 | 30000
...
01.01.2018 | 2 | 1 | 0.65 | 1
01.02.2018 | 2 | 1 | 0.83 | 1
01.03.2018 | 2 | 1 | 1.33 | 1
...
01.01.2018 | 2 | 2 | 55312 | 30000
01.02.2018 | 2 | 2 | 4503 | 30000
-----
As the example shows there are up to multiple Locations and KPI's. I was wondering if there is a way to write a measure to calculate the year to date values of the Actual / Target measures, seperating on Location and KPI? Is it possible in the table in its current state, or would I have to split the data by Location and KPI?
Wanted result:
-----
Date | Location | KPI | Actual | Target | Actual YTD | Actual AVG YTD |
01.01.2018 | 1 | 1 | 0.94 | 1 | 0.94 | 0.94
01.02.2018 | 1 | 1 | 1.02 | 1 | 1.96 | 0.98
01.03.2018 | 1 | 1 | 0.78 | 1 | 2.74 | 0.91
...
01.01.2018 | 1 | 2 | 34812 | 30000 | 34812 | 34812
01.02.2018 | 1 | 2 | 38992 | 30000 | 73804 | 36902
...
01.01.2018 | 2 | 1 | 0.65 | 1 | 0.65 | 0.65
01.02.2018 | 2 | 1 | 0.83 | 1 | 1.48 | 0.74
01.03.2018 | 2 | 1 | 1.33 | 1 | 3.16 | 1.05
...
01.01.2018 | 2 | 2 | 55312 | 30000 | 55312 | 55312
01.02.2018 | 2 | 2 | 4503 | 30000 | 59815 | 29907
-----
Best regards
arefossa
Solved! Go to Solution.
Hi arefossa,
To achieve your requirement, please follow steps below:
Click query editor-> Add Column-> Index Column, after applied&closed, create a calculate column using DAX below:
Actual YTD = CALCULATE(SUM(Table1[Actual]), FILTER(Table1, Table1[Location] = EARLIER(Table1[Location]) && Table1[KPI] = EARLIER(Table1[KPI]) && Table1[Index] <= EARLIER(Table1[Index])))
Then create a rank column based on Location and KPI columns:
Rank = RANKX(FILTER(Table1, Table1[Location] = EARLIER(Table1[Location]) && Table1[KPI] = EARLIER(Table1[KPI])), Table1[Actual YTD], ,ASC)
Finnally, achieve the average using DAX below:
Actual AVG YTD = Table1[Actual YTD] / Table1[Rank]
You can also refer to the sample file in the attachment.
Regards,
Jimmy Tao
Hi arefossa,
To achieve your requirement, please follow steps below:
Click query editor-> Add Column-> Index Column, after applied&closed, create a calculate column using DAX below:
Actual YTD = CALCULATE(SUM(Table1[Actual]), FILTER(Table1, Table1[Location] = EARLIER(Table1[Location]) && Table1[KPI] = EARLIER(Table1[KPI]) && Table1[Index] <= EARLIER(Table1[Index])))
Then create a rank column based on Location and KPI columns:
Rank = RANKX(FILTER(Table1, Table1[Location] = EARLIER(Table1[Location]) && Table1[KPI] = EARLIER(Table1[KPI])), Table1[Actual YTD], ,ASC)
Finnally, achieve the average using DAX below:
Actual AVG YTD = Table1[Actual YTD] / Table1[Rank]
You can also refer to the sample file in the attachment.
Regards,
Jimmy Tao
Hi @v-yuta-msft
This worked for the current year YTD over multiple dimensions. How can you calculate the same YTD, QTD, MTD for the last year? I tried multiple functions like sameperiodlastyear or dateadd however, its not working. Could you please help.
Hi Jimmy,
This worked for the current year YTD over multiple dimensions. How can you calculate the same YTD, QTD, MTD for the last year? I tried multiple functions like sameperiodlastyear or dateadd however, its not working. Could you please help.
This solved my task and was exactly what I needed!
Thanks alot!
Hi @Anonymous
It should be feasible. If you set up your table/matrix visual as you've shown, you can use a CALCULATE with a filter argument like:
Table1[Date]<=MAX(Table1[Date])
For instance, for the average:
Actual AVG YTD = CALCULATE ( AVERAGE ( Table1[KPI] ), Table1[Date] <= MAX ( Table1[Date] ) )
Hi AlB!
Thanks for your reply, but there seems to be some issues with your suggestion. The [KPI] values are just Id's for the KPI dimension along with the [Location] values and not used for aggregations. The columns I wish to get the YTD and Average YTD values for are 'Actual' and 'Target'.
To clearify, I wish to get the 'Actual YTD' / 'Actual AVG YTD' for each KPI at each Location, and hopefully in the current table structure.
@Anonymous
Ok, so then just update the column name you are doing the calculation over, as in:
Actual AVG YTD = CALCULATE ( AVERAGE ( Table1[Actual] ), Table1[Date] <= MAX ( Table1[Date] ) )
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |