Forum Discussion

vsp_p1's avatar
vsp_p1
Frequent Visitor
4 years ago
Solved

Show total in table conditionally

Dear experts,

I have a report that shows balances from customers. Currently, it shows the balance in only one currency but I want to let the users see the balance in their own currency. In this case, the total when multiple currencies are involved doesn't make any sense.

So, the idea is to show the total if only one currency is in scope and hide it in the other scenario.

In the following screenshot, when no country is filtered, the total should not be shown. But when a country is selected and it has only one currency (example - USA), it should show the sum. Can the experts here help me solve this?

 

Desired outputs:

The pbix can be found at https://1drv.ms/u/s!ArqTW_3LfZ4igh5JG2G0H-5MzVi3?e=BhfZby

Cheers, Vidyadutt

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi vsp_p1 ,

     

    I suggest you to create [Balance] measure by ISINSCOPE() and IF() function.

    Measure:

    Balance New = 
VAR _Balance = SUM('Table'[Sale Amount]) - SUM('Table'[Payment])
VAR _Count_List = COUNTAX(VALUES('Table'[Currency]),[Currency])
RETURN
IF(_Count_List = 1, _Balance, IF(ISINSCOPE('Table'[Country]),_Balance,BLANK()))

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi vsp_p1 ,

     

    I suggest you to create [Balance] measure by ISINSCOPE() and IF() function.

    Measure:

    Balance New = 
VAR _Balance = SUM('Table'[Sale Amount]) - SUM('Table'[Payment])
VAR _Count_List = COUNTAX(VALUES('Table'[Currency]),[Currency])
RETURN
IF(_Count_List = 1, _Balance, IF(ISINSCOPE('Table'[Country]),_Balance,BLANK()))

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.