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.
Hello,
I have this kind of data:
STOCK PERIOD VOLUME
AAA Q1 150
AAA Q2 2000
AAA Q3 0
AAA Q4 50
BBB Q1 300
BBB Q2 250
BBB Q3 15
BBB Q4 1000
CCC Q1 200
CCC Q2 600
CCC Q3 85
CCC Q4 140
On the report I have time slicer. Generally I want to choose for example Q2-Q4 and want DAX (RANKX) to SUM Volumes of Stock in selected period and give me Rank of the stocks.
Any idea how to solve this?
Solved! Go to Solution.
Hi @iwf
try :
Ranking = IF( AND( COUNTROWS( Data ) > 0, HASONEVALUE( Stocks[STOCK] ) ), RANKX( CALCULATETABLE( SUMMARIZE( Data, Stocks[STOCK], Quarters[PERIOD] ), ALLSELECTED() ), CALCULATE( SUM( Data[VOLUME] ) ) ) )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Hi @iwf
try :
Ranking = IF( AND( COUNTROWS( Data ) > 0, HASONEVALUE( Stocks[STOCK] ) ), RANKX( CALCULATETABLE( SUMMARIZE( Data, Stocks[STOCK], Quarters[PERIOD] ), ALLSELECTED() ), CALCULATE( SUM( Data[VOLUME] ) ) ) )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
@iwf Please try this as a "New Measure"
Test150Rnk = RANKX(ALLSELECTED(Test150DynRnk),CALCULATE(SUM(Test150DynRnk[Volume])),,DESC,Dense)
Proud to be a PBI Community Champion
Hi,
thank you for advice. But it gives me error: A circular dependency was detected: Tradelog(Ranktest). Dont know why
Hello @iwf
which solution is giving you the circular dependency?
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Test150Rnk = RANKX(ALLSELECTED(Test150DynRnk),CALCULATE(SUM(Test150DynRnk[Volume])),,DESC,Dense)
I don thave the separate data for Stock. I know, I can create new query and also calculatetable for dates, but I dont wanted to create two additional tables. So I tried the "simplier" solution. Also the formula was easier for me to understand.
Hello @iwf
the Stocks and Quartes tables just contain the unique values coming the main table of Stocks and Querters respectively
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
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 |
---|---|
109 | |
95 | |
77 | |
65 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |