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 All,
I have a scenario where i need to calcualte the top 5 sales based on some slicer values selections.
The measures that i have created as below.
_Ticket Sales = IF(SUM(Table[actl_plan_tkt_sls_amt])=0,BLANK(),SUM(Table[actl_plan_tkt_sls_amt]))
to get top 5 sales values
_Top Ticket Sales =
VAR Ranks =
IF(NOT ( ISBLANK ( [_Ticket Sales] ) ),RANKX( FILTER( ALLSELECTED(Table[cust_list_desc], Table[Size Grp], Table[size],Table[year],Table[Period x Week],Table[hol_ind],Table[promo_type_descp],Table[Event Name],Table[MB-OB-ARP],Table[plan_evnt_num]) ,NOT ( ISBLANK ( [_Ticket Sales] ) ) ),
[_Ticket Sales], , DESC, Dense ))
VAR Top5 = IF(Ranks <= SELECTEDVALUE('Top/Bottom'[Top/Bottom]),[_Ticket Sales],BLANK())
RETURN
SWITCH(TRUE(),SELECTEDVALUE('Measure'[Measure]) = "Tkt $" ,Top5,
SELECTEDVALUE('Measure'[Measure]) <> "Tkt $" && [_Ticket Sales] = BLANK(),0,[_Ticket Sales])
Result
Now i have another measure to calculate change in ticket sales, so i used below dax to create the measure.
Change in Ticket Sales in TB Events 1 =
VAR selectedvalue = SELECTEDVALUE('Final Feature Price'[Price Combined])
var fINAL =
CALCULATE(
[Avg Tkt $],
FILTER(ALLSELECTED(Table[cust_list_desc],Table[size],Table[year],Table[Period x Week],Table[Event Name],Table[promo_type_descp],Table[hol_ind],Table[plan_evnt_num],Table[MB-OB-ARP]),
Table[MB-OB-ARP] = selectedvalue))
RETURN
fINAL
the output which i am getting is correct as below.
But as you see in above image, when i include Change in Tciket slaes TB event 1 measure i see the blank values in Top Ticket Sales measure beasure of the conditions that i mentioned.
Now when i filter out the blanks in top ticket sales, the values of Change in Ticket Sales TB event 1 are changing which is not correct as below.
Did tried the other ways but non of them worked.
Any help or suggestion on how i can get this done.
thanks in advance.
Mohan V.
Hi @Anonymous ,
You can try this formula. Can you provide your PBIX file if it isn’t the output you want? That will help us solve the problem faster.
Change in Ticket Sales in TB Events 1 =
VAR selectedvalue = SELECTEDVALUE('Final Feature Price'[Price Combined])
var fINAL =
CALCULATE(
[Avg Tkt $],
FILTER(
ALLSELECTED(
Table[cust_list_desc],Table[size],Table[year],Table[Period x Week],Table[Event Name],
Table[promo_type_descp],Table[hol_ind],Table[plan_evnt_num],Table[MB-OB-ARP]
),
Table[MB-OB-ARP] = selectedvalue
)
)
RETURN
IF(ISBLANK(_Top Ticket Sales),BLANK(),fINAL)
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
106 | |
104 | |
79 | |
68 | |
61 |
User | Count |
---|---|
144 | |
104 | |
103 | |
82 | |
70 |