Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I got a fact table and date as a dimension table. I calculated three measures related to registration using Fact table. One measure is total registration in last FY, the second one is about total registration in YTD (means current financial year), and last one is about total registration in Last month. I named my measure as follow:
New Registration - Last FY = ...........
New Registration - YTD = ...............
New Registration- Last Month = ...........
I used these measure to plot the bar chart as shown in the following figure:
I want to show the measure name in the graph in such a way that if it is 'New Registration - Last FY' then it should be shown as 'FY21' or if it is 'New Registration - YTD' then it should show as 'FY22'. My desired output looks like as follow:
Is there any way of doing that dynamically in Power BI?
Solved! Go to Solution.
Hi @Dunner2020
The X Axis column is dynamicly. I add a sort column in calculated table, then we can use sort in measure to determind dynamic FY, FY YTD and Month.
X Axis =
VAR _T =
{
"FY" & ""
& FORMAT ( TODAY (), "yy" ) & " " & "YTD",
"FY" & ""
& FORMAT ( TODAY (), "yy" ) - 1,
"Mon" & ""
& FORMAT ( TODAY (), "mm" ) - 1
}
VAR _T2 =
ADDCOLUMNS (
_T,
"Sort",
IF (
CONTAINSSTRING ( [Value], "YTD" ),
2,
IF ( CONTAINSSTRING ( [Value], "Mon" ), 3, 1 )
)
)
RETURN
_T2
FY will always show FY+Last year number, FY YTD will alway show FY + current year +YTD, and mon will alway show mon + last month.
Ex:
Year = 2021 month = 10, it will show FY 2020(last year), FY 2021 YTD(current year) and Mon9(last month ),
Year = 2021 month = 11, it will show FY 2020(last year), FY 2021 YTD(current year) and Mon10(last month ),
Measure:
Measure = SWITCH(SUM('X Axis'[Sort]),1,[New Registration - Last FY],2,[New Registration - YTD],3,[New Registration- Last Month])
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Dunner2020
If you only add three measures into the value field to create the column chart, you couldn't see measure name in X aixs.
If you want to show names in X axis, you need to add columns in X axis. I suggest you to create a caluclated table with all names you need. Then create a new measure to combine three measures to one by names.
X Axis = {"FY"&""&FORMAT(TODAY(),"yy"),"FY"&""&FORMAT(TODAY(),"yy")-1,"Mon"&""&FORMAT(TODAY(),"mm")-1}
Then new measure is as below.
Measure = SWITCH(MAX('X Axis'[X axis]),"FY20",[New Registration - Last FY],"FY21",[New Registration - YTD],"Mon9",[New Registration- Last Month])
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-rzhou-msft , Thanks for your reply.
The only issue with the solution is that I have to change the name of the month every month in the measure. Otherwise, it would not work.
Hi @Dunner2020
The X Axis column is dynamicly. I add a sort column in calculated table, then we can use sort in measure to determind dynamic FY, FY YTD and Month.
X Axis =
VAR _T =
{
"FY" & ""
& FORMAT ( TODAY (), "yy" ) & " " & "YTD",
"FY" & ""
& FORMAT ( TODAY (), "yy" ) - 1,
"Mon" & ""
& FORMAT ( TODAY (), "mm" ) - 1
}
VAR _T2 =
ADDCOLUMNS (
_T,
"Sort",
IF (
CONTAINSSTRING ( [Value], "YTD" ),
2,
IF ( CONTAINSSTRING ( [Value], "Mon" ), 3, 1 )
)
)
RETURN
_T2
FY will always show FY+Last year number, FY YTD will alway show FY + current year +YTD, and mon will alway show mon + last month.
Ex:
Year = 2021 month = 10, it will show FY 2020(last year), FY 2021 YTD(current year) and Mon9(last month ),
Year = 2021 month = 11, it will show FY 2020(last year), FY 2021 YTD(current year) and Mon10(last month ),
Measure:
Measure = SWITCH(SUM('X Axis'[Sort]),1,[New Registration - Last FY],2,[New Registration - YTD],3,[New Registration- Last Month])
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
You can do that with a Calculation Group. Create a Calculation Item associated with each measure and name the item how you want it displayed, and then use the "Name" column of the calculation group as the x axis in your visual.
Are you still not using Calculation Groups?! - YouTube
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
117 | |
101 | |
71 | |
61 |