cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kirankumar3065
Frequent Visitor

Need help to plot table / Metric in Power BI

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.

 

ISINClientBuy/SellAmount
XYZAirtelBuy100
XYZIdeaSell-100
XYZDocomoBuy100
XYZAirtelSell-100
ZYXHutchBuy200
ZYXIdeaSell-200
ZYXDocomoBuy200
ZYXHutchSell-200
ABCAircelBuy500
ABCIdeaSell-500
CBDSpiceBuy150
CBDSingtelSell-150

 

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

 

ISINClientBuySellGrand Total
XYZAirtel100-1000
 Docomo100 100
 Idea -100-100
XYZ Total 200-2000
ZYXDocomo200 200
 Hutch200-2000
 Idea -200-200
ZYX Total 400-4000

 

Regards,

Kiran

 

2 ACCEPTED SOLUTIONS
Jihwan_Kim
Super User
Super User

Hi, @kirankumar3065 

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

 

Picture2.png

 

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

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


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.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

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

 

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

4 REPLIES 4
Jihwan_Kim
Super User
Super User

Hi, @kirankumar3065 

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

 

Picture2.png

 

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

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


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.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

parry2k
Super User
Super User

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






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





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

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

 

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!