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 Team,
In my Matrix is contained Year, City, and Revenue and the matrix is filter Top 2 Years.
If based on the customer selections Matrix years are changed.
Suppose: If I select customer is 219282 then the Last 2 years' data revenue (Year: 2022 is 10M and Year 2021: is 20M)
then compare the lastest last 2 years' revenue to show the difference in arrows(If the lastest top 1 year is greater than the latest top 2 then the green arrow else the red arrow).
How to create the above conditional formatting based on dynamic change Years(Lastest last year's data based on the customer selection)
Thanks,
chandrasekhar
Solved! Go to Solution.
Hi @sekhar438 ,
Here are the steps you can follow:
1. Create calculated table.
True1 =
var _table1=
SUMMARIZE(
'Table','Table'[customer],'Table'[Year],'Table'[Attribute], "Value",SUMX(FILTER(ALL('Table'),'Table'[customer]=EARLIER('Table'[customer])&&'Table'[Year]=EARLIER('Table'[Year])&&'Table'[Attribute]=EARLIER('Table'[Attribute])),[Value]),
"Rank",RANKX(FILTER(ALL('Table'),'Table'[customer]=EARLIER('Table'[customer])),[Year],,ASC,Dense))
var _table2=
FILTER(_table1,[Rank]<=2)
return
SUMMARIZE(
_table2,[customer],[Attribute],[Year],[Value])
True2 =
VAR _table1 =
SUMMARIZE (
'True1',
'True1'[customer],
'True1'[Attribute],
"Year", "Status",
"Value",
IF (
SUMX (
FILTER (
ALL ( True1 ),
'True1'[customer] = EARLIER ( 'True1'[customer] )
&& 'True1'[Attribute] = EARLIER ( 'True1'[Attribute] )
&& 'True1'[Year]
= MINX (
FILTER (
'True1',
'True1'[customer] = EARLIER ( 'True1'[customer] )
&& 'True1'[Attribute] = EARLIER ( 'True1'[Attribute] )
),
[Year]
)
),
[Value]
)
<= SUMX (
FILTER (
ALL ( True1 ),
'True1'[customer] = EARLIER ( 'True1'[customer] )
&& 'True1'[Attribute] = EARLIER ( 'True1'[Attribute] )
&& 'True1'[Year]
= MAXX (
FILTER (
'True1',
'True1'[customer] = EARLIER ( 'True1'[customer] )
&& 'True1'[Attribute] = EARLIER ( 'True1'[Attribute] )
),
[Year]
)
),
[Value]
),
UNICHAR ( 9650 ),
UNICHAR ( 128315 )
)
)
RETURN
UNION ( 'True1', _table1 )
2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @sekhar438 ,
Here are the steps you can follow:
1. Create calculated table.
True1 =
var _table1=
SUMMARIZE(
'Table','Table'[customer],'Table'[Year],'Table'[Attribute], "Value",SUMX(FILTER(ALL('Table'),'Table'[customer]=EARLIER('Table'[customer])&&'Table'[Year]=EARLIER('Table'[Year])&&'Table'[Attribute]=EARLIER('Table'[Attribute])),[Value]),
"Rank",RANKX(FILTER(ALL('Table'),'Table'[customer]=EARLIER('Table'[customer])),[Year],,ASC,Dense))
var _table2=
FILTER(_table1,[Rank]<=2)
return
SUMMARIZE(
_table2,[customer],[Attribute],[Year],[Value])
True2 =
VAR _table1 =
SUMMARIZE (
'True1',
'True1'[customer],
'True1'[Attribute],
"Year", "Status",
"Value",
IF (
SUMX (
FILTER (
ALL ( True1 ),
'True1'[customer] = EARLIER ( 'True1'[customer] )
&& 'True1'[Attribute] = EARLIER ( 'True1'[Attribute] )
&& 'True1'[Year]
= MINX (
FILTER (
'True1',
'True1'[customer] = EARLIER ( 'True1'[customer] )
&& 'True1'[Attribute] = EARLIER ( 'True1'[Attribute] )
),
[Year]
)
),
[Value]
)
<= SUMX (
FILTER (
ALL ( True1 ),
'True1'[customer] = EARLIER ( 'True1'[customer] )
&& 'True1'[Attribute] = EARLIER ( 'True1'[Attribute] )
&& 'True1'[Year]
= MAXX (
FILTER (
'True1',
'True1'[customer] = EARLIER ( 'True1'[customer] )
&& 'True1'[Attribute] = EARLIER ( 'True1'[Attribute] )
),
[Year]
)
),
[Value]
),
UNICHAR ( 9650 ),
UNICHAR ( 128315 )
)
)
RETURN
UNION ( 'True1', _table1 )
2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hello @sekhar438 ,
You can create a below measure that will return 1 or 0 based on condition and then can have conditional formatting applied based on the values. In the below case if it is 0 then green arrow as icon and if it is 1 then red arrow as a icon
Ordered Qty = SUM(Fact[OrderQty])
_ConditionalFormatting =
VAR SelMinYearOrder = CALCULATE([Ordered Qty],date[The_Year]=MIN(date[The_Year]))
Var SelMaxYearOrder = CALCULATE([Ordered Qty],date[The_Year]=MAX(date[The_Year]))
RETURN
IF ( SelMaxYearOrder < SelMinYearOrder ,1,0)
Please let me know if it doesn't help out to resolve the problem.
Hi MuskanAgarwal ,
Thanks for your reply.
I have checked the above share calculation and getting the below output,
note: all values
Required output:
In this table Years(latest last 2 years) are changed based on the Customer filter selection,
If Customer: 1002 then,
If Customer 1004
Thanks,
Chandrasekhar
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |