Need help to plot table / Metric in Power BI

[Anonymous]

Hi Team,

i am trying to build a report in power BI below is the sample data.

My clients are Airtel and Hutch(Highlited in Red). i need to filter out the ISIN's which are related only to "Airtel" and "Hutch"(in real scenario i have 14 clients and N number of ISIN's). When i filter the realted ISIN's i should not miss out the row of Idea and Docomo.

Expected output shown in second table.

ISIN	Client	Buy/Sell	Amount
XYZ	Airtel	Buy	100
XYZ	Idea	Sell	-100
XYZ	Docomo	Buy	100
XYZ	Airtel	Sell	-100
ZYX	Hutch	Buy	200
ZYX	Idea	Sell	-200
ZYX	Docomo	Buy	200
ZYX	Hutch	Sell	-200
ABC	Aircel	Buy	500
ABC	Idea	Sell	-500
CBD	Spice	Buy	150
CBD	Singtel	Sell	-150

ISIN ABC & CBD can be ignored as my clients are only Airtel and Hutch.

ISIN	Client	Buy	Sell	Grand Total
XYZ	Airtel	100	-100	0
	Docomo	100		100
	Idea		-100	-100
XYZ Total		200	-200	0
ZYX	Docomo	200		200
	Hutch	200	-200	0
	Idea		-200	-200
ZYX Total		400	-400	0

Regards,

Kiran

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

Please check the below picture and the sample pbix file's link down below.

Amount Total =

SUM('Table'[Amount])

Amount by condition measure =

VAR conditions = { "Airtel", "Hutch" }

VAR currentISIN =

MAX ( 'Table'[ISIN] )

VAR tablebycondition =

ADDCOLUMNS (

CROSSJOIN (

SUMMARIZE (

FILTER (

ALL ( 'Table' ),

'Table'[Client]

IN conditions

&& 'Table'[ISIN] = currentISIN

),

'Table'[ISIN]

),

VALUES ( 'Table'[Client] )

),

"@amount", [Amount Total]

)

RETURN

SUMX ( tablebycondition, [@amount] )

https://www.dropbox.com/s/09x2lizbq89bspr/kirankumar.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

Please check the below picture and the sample pbix file's link down below.

Amount Total =

SUM('Table'[Amount])

Amount by condition measure =

VAR conditions = { "Airtel", "Hutch" }

VAR currentISIN =

MAX ( 'Table'[ISIN] )

VAR tablebycondition =

ADDCOLUMNS (

CROSSJOIN (

SUMMARIZE (

FILTER (

ALL ( 'Table' ),

'Table'[Client]

IN conditions

&& 'Table'[ISIN] = currentISIN

),

'Table'[ISIN]

),

VALUES ( 'Table'[Client] )

),

"@amount", [Amount Total]

)

RETURN

SUMX ( tablebycondition, [@amount] )

https://www.dropbox.com/s/09x2lizbq89bspr/kirankumar.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[parry2k]

@Anonymous why not you use just have a page level or report level filter and only select your clients. Am I missing something?

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

Hi,

Problem is i want to filter on the ISIN's which belong to my Clients. i don not want to miss any clients under the ISIN  filtered for my clients.

Regards,

Kiran

[parry2k · Accepted solution]

@Anonymous Got your point, just add a simple measure as below, replace column and table name as per your model and use this measure in matrix visual:

Measure = 
VAR __myClient = { "Airtel", "Hutch" } 
VAR __myISIN = CALCULATETABLE ( VALUES ( ISIN[ISIN] ), ISIN[Client] IN __myClient )
RETURN
CALCULATE ( SUM ( ISIN[Amount] ), TREATAS ( __myISIN, ISIN[ISIN] ) )

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡