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.
I have dataset in table 1, then I make table 2 in powerbi. How can I make growth colu
Hi @basyaiban ,
You can add a new table with all year and growth and create a matrix with original table id on row, brand and new table column on columns, then write a measure to use current row content to lookup corresponding values.
If you confused about coding formula, please share some sample data as table format for test.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
@v-shex-msft Sorry. I gave you the wrong data yesterday. Here's my data.
ID | Quarter | Year | Sales |
a | 1 | 2016 | 51409.1 |
a | 1 | 2017 | 568727 |
a | 2 | 2016 | 556365 |
a | 2 | 2017 | 79091.8 |
b | 1 | 2016 | 127820 |
b | 1 | 2017 | 836365 |
b | 2 | 2016 | 261000 |
b | 2 | 2017 | 172727 |
I wanna make this
ID | Quarter 1 | Quarter 2 | ||||
2016 | 2017 | Growth | 2016 | 2017 | Growth | |
a | ||||||
b |
But I got this
ID | Quarter 1 | Quarter 2 | ||||||
2016 | Growth | 2017 | Growth | 2016 | Growth | 2017 | Growth | |
a | ||||||||
b |
HI @basyaiban ,
You can refer to the following steps to achieve your requirement:
Steps:
1. Create a new table to extract year from the original table and add 'growth'.
T2 =
UNION ( VALUES ( T1[Year] ), { "Growth" } )
2. Create a matrix visual with T1[id] as row, T1[quarter], T2[Year] as column. (Notice: current it does not work because these fields can;t mapping correctly)
3. Write a measure to use correspond row/column labels as conditions to calculate and use on value fields of matrix.
Measure 2 =
VAR currYear =
SELECTEDVALUE ( T2[Year] )
RETURN
IF (
ISFILTERED ( T2[Year] )
&& currYear <> "Growth",
CALCULATE (
SUM ( T1[Sales] ),
FILTER ( ALLSELECTED ( T1 ), [Year] = VALUE ( currYear ) ),
VALUES ( T1[Quarter] ),
VALUES ( T1[ID] )
),
VAR list =
SELECTCOLUMNS (
EXCEPT ( ALL ( T2[Year] ), VALUES ( T2[Year] ) ),
"Y", VALUE ( [Year] )
)
VAR _min =
CALCULATE (
SUM ( T1[Sales] ),
FILTER ( ALLSELECTED ( T1 ), [Year] = MINX ( list, [Y] ) ),
VALUES ( T1[Quarter] ),
VALUES ( T1[ID] )
)
VAR _max =
CALCULATE (
SUM ( T1[Sales] ),
FILTER ( ALLSELECTED ( T1 ), [Year] = MAXX ( list, [Y] ) ),
VALUES ( T1[Quarter] ),
VALUES ( T1[ID] )
)
RETURN
DIVIDE ( _max - _min, _min )
)
Regards,
Xiaoxin Sheng
@v-shex-msft thank you. it works.
I forgot that I must calculate the YTD too.
I make measure "YTD = TOTALYTD(SUM(T1[Sales),T1[Date])"
But I can't apply this because YTD is measure (sales is column), it can't work because there's SUM in the formula.
Can I make the growth of YTD (in case : YTD is a measure not column).
Thanks in advance.
Hi @basyaiban ,
If you mean you want to rolling calculate between 'quarter' fields of the same year, you can try to use following measure formula. I remove old quarter filter and add a rolling quarter filter instead.
Measure 2 =
VAR currYear =
SELECTEDVALUE ( T2[Year] )
RETURN
IF (
ISFILTERED ( T2[Year] )
&& currYear <> "Growth",
CALCULATE (
SUM ( T1[Sales] ),
FILTER (
ALLSELECTED ( T1 ),
[Year] = VALUE ( currYear )
&& [Quarter] <= MAX ( T1[Quarter] )
),
VALUES ( T1[ID] )
),
VAR list =
SELECTCOLUMNS (
EXCEPT ( ALL ( T2[Year] ), VALUES ( T2[Year] ) ),
"Y", VALUE ( [Year] )
)
VAR _min =
CALCULATE (
SUM ( T1[Sales] ),
FILTER (
ALLSELECTED ( T1 ),
[Year] = MINX ( list, [Y] )
&& [Quarter] <= MAX ( T1[Quarter] )
),
VALUES ( T1[ID] )
)
VAR _max =
CALCULATE (
SUM ( T1[Sales] ),
FILTER (
ALLSELECTED ( T1 ),
[Year] = MAXX ( list, [Y] )
&& [Quarter] <= MAX ( T1[Quarter] )
),
VALUES ( T1[ID] )
)
RETURN
DIVIDE ( _max - _min, _min )
)
Regards,
Xioaxin Sheng
Measure 2 = VAR currYear = SELECTEDVALUE ( T2[Year] ) RETURN IF ( ISFILTERED ( T2[Year] ) && currYear <> "Growth", CALCULATE ( SUMX ( T1,[YTD] ), FILTER ( ALLSELECTED ( T1 ), [Year] = VALUE ( currYear ) ), VALUES ( T1[Quarter] ), VALUES ( T1[ID] ) ), VAR list = SELECTCOLUMNS ( EXCEPT ( ALL ( T2[Year] ), VALUES ( T2[Year] ) ), "Y", VALUE ( [Year] ) ) VAR _min = CALCULATE ( SUMX ( T1,[YTD] ), FILTER ( ALLSELECTED ( T1 ), [Year] = MINX ( list, [Y] ) ), VALUES ( T1[Quarter] ), VALUES ( T1[ID] ) ) VAR _max = CALCULATE ( SUMX ( T1,[YTD] ), FILTER ( ALLSELECTED ( T1 ), [Year] = MAXX ( list, [Y] ) ), VALUES ( T1[Quarter] ), VALUES ( T1[ID] ) ) RETURN DIVIDE ( _max - _min, _min ) )
HI @basyaiban ,
Can you please share a sample pbix file for test? It is hard to test without any detail data.
Notice: do mask on sensitive data before share.
Regards,
Xiaoxin Sheng
there is SUM ( T1[Sales] ) in your formula. It can running because Sales is a column.
But, I can't running SUM ( [YTD] ) because its a measure. I add new measure called YTD (Year to Date). YTD is calculated from YTD = TOTALYTD(SUM(T1[Sales]),T1[Date])
I want to make table like this. But the value is not sales again. Its value is from YTD
ID | Quarter 1 | Quarter 2 | ||||
2016 | 2017 | Growth | 2016 | 2017 | Growth | |
a | ||||||
b |
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 | |
105 | |
88 | |
74 | |
66 |
User | Count |
---|---|
126 | |
111 | |
100 | |
83 | |
71 |