Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
vjnvinod
Impactful Individual
Impactful Individual

Tricky Power BI question

Dear Community,

 

Below is my table in the Page 1 of power BI sofware

 

SLTP
AD311628
AS137153
CB-65774
TA117019
Tx152892
Total652918

 

I need 2 solutions

 

1) an additonal measure which calculates my Running %, see my output table below which i want to achieve(below i did manually in excel)

 

Required Output in %
48%
21%
-10%
18%
23%
100%

 

2) Automated text which changes dynamically based on the value 

"Ad constitute 48% of total pipeline value, followed by Tx 23%"

 

Let me know how to achieve this

1 ACCEPTED SOLUTION
Stachu
Community Champion
Community Champion

I think the issue was with scenario where there is only 1 entry meeting the criteria, try this code

Measure = 
VAR _Summary = ADDCOLUMNS(VALUES('Table'[SL]),"Value",[TP_SUM],"%",[Running %])
VAR _Top2 = TOPN(2,_Summary,[%],DESC)
VAR _FirstValue = MAXX(_Top2,[%])
VAR _FirstName = FILTER(_Top2,[%]=_FirstValue) 
VAR _SecondValue = MINX(_Top2,[%])
VAR _SecondName = FILTER(_Top2,[%]=_SecondValue)
RETURN
CONCATENATEX(_FirstName,[SL]) & " constitute " & FORMAT(_FirstValue, "Percent") & " of total pipeline value, followed by " & CONCATENATEX(_SecondName,[SL]) & " " & FORMAT(_SecondValue, "Percent")


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

View solution in original post

11 REPLIES 11
Stachu
Community Champion
Community Champion

assuming  the first table you posted is your data table, add following measures

TP_SUM = SUM('Table'[TP])

Running % = 
VAR CurrentTP = [TP_SUM]
VAR TotalTP = CALCULATE([TP_SUM],ALLSELECTED('Table'))
RETURN
DIVIDE(CurrentTP,TotalTP)

Measure = 
VAR _Summary = SUMMARIZECOLUMNS('Table'[SL],"Value",[TP_SUM],"%",[Running %])
VAR _Top2 = TOPN(2,_Summary,[%],DESC)
VAR _FirstValue = MAXX(_Top2,[%])
VAR _FirstName = FILTER(_Top2,[%]=_FirstValue) 
VAR _SecondValue = MINX(_Top2,[%])
VAR _SecondName = FILTER(_Top2,[%]=_SecondValue)
RETURN
CONCATENATEX(_FirstName,[SL]) & " constitute " & FORMAT(_FirstValue, "Percent") & " of total pipeline value, followed by " & CONCATENATEX(_SecondName,[SL]) & " " & FORMAT(_SecondValue, "Percent")

then you could set it up like that (with Measure in the card visual)

 

Capture.PNG



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

vjnvinod
Impactful Individual
Impactful Individual

@Stachu

 

Hi, thanks,

 

Can you share the pbix file?

Stachu
Community Champion
Community Champion

there you go
https://1drv.ms/u/s!AjxUGXgGNzCEiV00A6MmIIZfC4U9



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

vjnvinod
Impactful Individual
Impactful Individual

@Stachu

 

Hi,

 

I did testing in my file and below is the error. and realised that error is coming because of adding an additonal filter where the status is Open

if you can help and let me know, that will be great, do we have to modify the measure?

 

I have added a filter where status is OpenI have added a filter where status is Openused slicer to understand if its changes dynamically for different industriesused slicer to understand if its changes dynamically for different industries

Stachu
Community Champion
Community Champion

how does your data table look like?
do the sum and running total work fine with the slicer and give correct values?



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

vjnvinod
Impactful Individual
Impactful Individual

@Stachu

 

The problem is with the "status" coloumn in my data table,

 

Status
Closed
On Hold
Open

when i drag and add that too visual filter and filter the "open", the measure which you have

created doesn't work.

 

i think you may need to add status filter in the measure, i am not sure how to do it

 

filtertext.PNG

Stachu
Community Champion
Community Champion

sorry but you've said that before and it doesn't clarify anything. In order to help I need additional details which I posted in my previous post

 

here is a post on how to phrase questions to get answers quickly
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

vjnvinod
Impactful Individual
Impactful Individual

@Stachu

Appologies for confusing, here is the below answer you requested for.

 

2) do the sum and running total work fine with the slicer and give correct values?

yes they work fine with the slicer

 

1) how does your data table look like?

here it is below

 

SLTPStatusIndustry
AD33951ClosedAdvanced Manufacturing
AD67775ClosedMining & Metals
AD7550ClosedOil & Gas
AD834037ClosedOther
AD96680ClosedPower & Utilities
AD16746ClosedReal Estate
AS11500ClosedAdvanced Manufacturing
AS171142ClosedMining & Metals
AS4372ClosedOil & Gas
AS203040ClosedOther
AS7887ClosedPower & Utilities
AS14181ClosedReal Estate
CB-8736ClosedAdvanced Manufacturing
CB-22625ClosedMining & Metals
CB-2888ClosedOil & Gas
CB-158825ClosedOther
CB-13369ClosedPower & Utilities
CB-10551ClosedReal Estate
TA10087ClosedAdvanced Manufacturing
TA29845ClosedMining & Metals
TA5843ClosedOil & Gas
TA284349ClosedOther
TA28270ClosedPower & Utilities
TA25086ClosedReal Estate
TX30939ClosedAdvanced Manufacturing
TX52795ClosedMining & Metals
TX11476ClosedOil & Gas
TX280048ClosedOther
TX16466ClosedPower & Utilities
TX28281ClosedReal Estate
AD969On HoldAdvanced Manufacturing
AD6051On HoldMining & Metals
AD18821On HoldOther
AD1347On HoldPower & Utilities
AD154On HoldReal Estate
AS455On HoldAdvanced Manufacturing
AS3071On HoldMining & Metals
AS5On HoldOil & Gas
AS7702On HoldOther
AS466On HoldPower & Utilities
AS40On HoldReal Estate
CB-1042On HoldAdvanced Manufacturing
CB-8288On HoldMining & Metals
CB-4660On HoldOther
CB-29On HoldPower & Utilities
CB-155On HoldReal Estate
TA1765On HoldAdvanced Manufacturing
TA12900On HoldOther
TA780On HoldPower & Utilities
TA144On HoldReal Estate
TX1546On HoldAdvanced Manufacturing
TX8876On HoldMining & Metals
TX455On HoldOil & Gas
TX11266On HoldOther
TX304On HoldPower & Utilities
TX892On HoldReal Estate
AD36534OpenAdvanced Manufacturing
AD47276OpenMining & Metals
AD6807OpenOil & Gas
AD898759OpenOther
AD80890OpenPower & Utilities
AD22100OpenReal Estate
AS45440OpenAdvanced Manufacturing
AS44881OpenMining & Metals
AS2428OpenOil & Gas
AS88430OpenOther
AS12238OpenPower & Utilities
AS11421OpenReal Estate
CB-5325OpenAdvanced Manufacturing
CB-8993OpenMining & Metals
CB-1572OpenOil & Gas
CB-143666OpenOther
CB-9861OpenPower & Utilities
CB-13244OpenReal Estate
TA7611OpenAdvanced Manufacturing
TA7961OpenMining & Metals
TA1455OpenOil & Gas
TA204188OpenOther
TA14068OpenPower & Utilities
TA8639OpenReal Estate
TX13671OpenAdvanced Manufacturing
TX17252OpenMining & Metals
TX9478OpenOil & Gas
TX224866OpenOther
TX11751OpenPower & Utilities
TX21052OpenReal Estate
Stachu
Community Champion
Community Champion

I think the issue was with scenario where there is only 1 entry meeting the criteria, try this code

Measure = 
VAR _Summary = ADDCOLUMNS(VALUES('Table'[SL]),"Value",[TP_SUM],"%",[Running %])
VAR _Top2 = TOPN(2,_Summary,[%],DESC)
VAR _FirstValue = MAXX(_Top2,[%])
VAR _FirstName = FILTER(_Top2,[%]=_FirstValue) 
VAR _SecondValue = MINX(_Top2,[%])
VAR _SecondName = FILTER(_Top2,[%]=_SecondValue)
RETURN
CONCATENATEX(_FirstName,[SL]) & " constitute " & FORMAT(_FirstValue, "Percent") & " of total pipeline value, followed by " & CONCATENATEX(_SecondName,[SL]) & " " & FORMAT(_SecondValue, "Percent")


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

vjnvinod
Impactful Individual
Impactful Individual

@Stachu

 

Thanks so much, this works.

 

Can you let me know, how do you learn this?

i know the basics of power BI, but cant write Measures, as its bit technical.

 

want to learn, if you could suggest, how to start & learn this

Stachu
Community Champion
Community Champion

this post offers tons of links:

https://community.powerbi.com/t5/Desktop/Good-Source-to-Learn-DAX/m-p/537748/highlight/true#M252389

As for myself I learned on the job, from online sources, then had an opportunity to take Mastering DAX training, which put my skill on the next level. Very steep learning curve, but also very rewarding. Book The Definitive Guide to DAX is companion book, it can give you great understanding of the language, but it's not an easy read.

 

as for Query Editor (M) I'm self taught. There is tons of good blogs, etc. and the moment you realize you can write your own functions, do recurrence etc. you have a really good grasp on what's possitble and it then is more about how to do it in the most efficent way 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.