Get Account Numbers which starts with specific numbers

[deeksha]

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

[vivran22 · Accepted solution]

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]

@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]

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 · Accepted solution]

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!

[deeksha]

Hi, Thanks for the reply, I tried the new formula but again it is showing something else. it should display only the accounts which are starting with 3,4 and 6 but it is showing some other values. 

[vivran22]

So you created the calculated column:

and then applied the filter on the visual:

still not getting the desired results?

[deeksha]

Yes I created a calculated column and applied it in the filters and I am getting the results I showed above in my report and in the table, it is showing like yours, a column with a value of 1

[vivran22]

Is it possible for you to share your pbix file with sample data (removing anything confidential)?

[deeksha]

@vivran22 what your formula is doing that it has created a new column and it is showing a value of 1 in front of accounts which start with 3,4 and 6 but what my requirement is it should display those account numbers which starts with the digits 3,4 and 6 and not a value of 1 for them.

[vivran22 · Accepted solution]

@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.

[deeksha]

@vivran22 Thank you soo much, I got the desired result, thank you for such quick responses and help 🙂