Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

How to avoid a visual level measure filter onto another measure calculations

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.

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

1 Reply

  • v-kkf-msft's avatar
    v-kkf-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    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.