Forum Discussion

deeksha's avatar
deeksha
Icon for Helper II rankHelper II
6 years ago
Solved

Get Account Numbers which starts with specific numbers

Hi all,

 

I am new to power bi and I am facing an issue, I want my report to only calculate the transaction amount of only those accounts which starts with the specific numbers like 3,4 and 6, how can I do that?

please help me out

Need Help
  • vivran22's avatar
    vivran22
    6 years ago

    Hi Deeksha,

     

    So, you want to filter your visuals with the account numbers starting with specific digits?

     

    In that case you might want to create a calculated column with the the formula:

     

    IF(LEFT(Sheet2[Account],1) in {"3","4","6"}, 1)
     
    and then use this column in Filters
     
    You may refer to the sample pbix file here
     
    Let me know if this is what you are looking for.
     
    Regards,
    Vivek
     
    If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions help users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!
  • vivran22's avatar
    vivran22
    6 years ago

    deekshaI am using the new column (Filtered Account in the sample pbix file I shared) as a support field to filter my visuals. I am not using Filtered Account column in any visuals, but to filter the visuals using the Filter pane

     

    I have created two visuals for your reference. Both the table has the same fields:

     

     The left table shows all the accounts (with a total of 488) and the right table shows account number starting with 3,4 & 6 (with a total of 252).

     

    How it has been achieved is through using the Calculated Column: Filter Account in the Filter Pane:

     

    Filter Pane for the left table:

     

     

    Filter pane for the right table:

     

     

    This is giving me the desired results.

     

    I hope this is what you are looking for.

10 Replies

  • vivran22's avatar
    vivran22
    Icon for Community Champion rankCommunity Champion
    6 years ago

    deeksha 

     

    Hi,

     

    You can try this:

     

     

    Sum selected = CALCULATE(SUM(Sheet2[Sum]),FILTER(Sheet2,LEFT(Sheet2[Account],1) in {"3","4","6"}))

     

     

    or

     

    Sum Sel 2 = SUMX(Sheet2,IF(LEFT(Sheet2[Account],1) in {"3","4","6"},Sheet2[Sum]))

     

    Regards,

    Vivek

     

    If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions help users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

     

    • deeksha's avatar
      deeksha
      Icon for Helper II rankHelper II
      6 years ago

      Hi, 

      Thank you for the reply, I tried your formula but it is giving any other value as you can see in the picture, I want it to filter the accounts and only display the accounts which start with 3 or 6 but it is calculating something else.

      • vivran22's avatar
        vivran22
        Icon for Community Champion rankCommunity Champion
        6 years ago

        Hi Deeksha,

         

        So, you want to filter your visuals with the account numbers starting with specific digits?

         

        In that case you might want to create a calculated column with the the formula:

         

        IF(LEFT(Sheet2[Account],1) in {"3","4","6"}, 1)
         
        and then use this column in Filters
         
        You may refer to the sample pbix file here
         
        Let me know if this is what you are looking for.
         
        Regards,
        Vivek
         
        If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions help users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!