Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Dear Community,
Below is my table in the Page 1 of power BI sofware
SL | TP |
AD | 311628 |
AS | 137153 |
CB | -65774 |
TA | 117019 |
Tx | 152892 |
Total | 652918 |
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
Solved! Go to Solution.
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")
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)
there you go
https://1drv.ms/u/s!AjxUGXgGNzCEiV00A6MmIIZfC4U9
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?
how does your data table look like?
do the sum and running total work fine with the slicer and give correct values?
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
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
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
SL | TP | Status | Industry |
AD | 33951 | Closed | Advanced Manufacturing |
AD | 67775 | Closed | Mining & Metals |
AD | 7550 | Closed | Oil & Gas |
AD | 834037 | Closed | Other |
AD | 96680 | Closed | Power & Utilities |
AD | 16746 | Closed | Real Estate |
AS | 11500 | Closed | Advanced Manufacturing |
AS | 171142 | Closed | Mining & Metals |
AS | 4372 | Closed | Oil & Gas |
AS | 203040 | Closed | Other |
AS | 7887 | Closed | Power & Utilities |
AS | 14181 | Closed | Real Estate |
CB | -8736 | Closed | Advanced Manufacturing |
CB | -22625 | Closed | Mining & Metals |
CB | -2888 | Closed | Oil & Gas |
CB | -158825 | Closed | Other |
CB | -13369 | Closed | Power & Utilities |
CB | -10551 | Closed | Real Estate |
TA | 10087 | Closed | Advanced Manufacturing |
TA | 29845 | Closed | Mining & Metals |
TA | 5843 | Closed | Oil & Gas |
TA | 284349 | Closed | Other |
TA | 28270 | Closed | Power & Utilities |
TA | 25086 | Closed | Real Estate |
TX | 30939 | Closed | Advanced Manufacturing |
TX | 52795 | Closed | Mining & Metals |
TX | 11476 | Closed | Oil & Gas |
TX | 280048 | Closed | Other |
TX | 16466 | Closed | Power & Utilities |
TX | 28281 | Closed | Real Estate |
AD | 969 | On Hold | Advanced Manufacturing |
AD | 6051 | On Hold | Mining & Metals |
AD | 18821 | On Hold | Other |
AD | 1347 | On Hold | Power & Utilities |
AD | 154 | On Hold | Real Estate |
AS | 455 | On Hold | Advanced Manufacturing |
AS | 3071 | On Hold | Mining & Metals |
AS | 5 | On Hold | Oil & Gas |
AS | 7702 | On Hold | Other |
AS | 466 | On Hold | Power & Utilities |
AS | 40 | On Hold | Real Estate |
CB | -1042 | On Hold | Advanced Manufacturing |
CB | -8288 | On Hold | Mining & Metals |
CB | -4660 | On Hold | Other |
CB | -29 | On Hold | Power & Utilities |
CB | -155 | On Hold | Real Estate |
TA | 1765 | On Hold | Advanced Manufacturing |
TA | 12900 | On Hold | Other |
TA | 780 | On Hold | Power & Utilities |
TA | 144 | On Hold | Real Estate |
TX | 1546 | On Hold | Advanced Manufacturing |
TX | 8876 | On Hold | Mining & Metals |
TX | 455 | On Hold | Oil & Gas |
TX | 11266 | On Hold | Other |
TX | 304 | On Hold | Power & Utilities |
TX | 892 | On Hold | Real Estate |
AD | 36534 | Open | Advanced Manufacturing |
AD | 47276 | Open | Mining & Metals |
AD | 6807 | Open | Oil & Gas |
AD | 898759 | Open | Other |
AD | 80890 | Open | Power & Utilities |
AD | 22100 | Open | Real Estate |
AS | 45440 | Open | Advanced Manufacturing |
AS | 44881 | Open | Mining & Metals |
AS | 2428 | Open | Oil & Gas |
AS | 88430 | Open | Other |
AS | 12238 | Open | Power & Utilities |
AS | 11421 | Open | Real Estate |
CB | -5325 | Open | Advanced Manufacturing |
CB | -8993 | Open | Mining & Metals |
CB | -1572 | Open | Oil & Gas |
CB | -143666 | Open | Other |
CB | -9861 | Open | Power & Utilities |
CB | -13244 | Open | Real Estate |
TA | 7611 | Open | Advanced Manufacturing |
TA | 7961 | Open | Mining & Metals |
TA | 1455 | Open | Oil & Gas |
TA | 204188 | Open | Other |
TA | 14068 | Open | Power & Utilities |
TA | 8639 | Open | Real Estate |
TX | 13671 | Open | Advanced Manufacturing |
TX | 17252 | Open | Mining & Metals |
TX | 9478 | Open | Oil & Gas |
TX | 224866 | Open | Other |
TX | 11751 | Open | Power & Utilities |
TX | 21052 | Open | Real Estate |
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")
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
105 | |
88 | |
74 | |
66 |
User | Count |
---|---|
126 | |
111 | |
100 | |
83 | |
71 |