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
Community Champion
Community Champion

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
Community Champion
Community Champion

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 III
Super User III

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

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors