Forum Discussion

vjnvinod's avatar
vjnvinod
Icon for Impactful Individual rankImpactful Individual
7 years ago
Solved

Tricky Power BI question

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 ...
Need Help
Show and Tell
  • Stachu's avatar
    Stachu
    7 years ago

    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")
vjnvinod's avatar
vjnvinod
Icon for Impactful Individual rankImpactful Individual
7 years ago

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's avatar
Stachu
Icon for Community Champion rankCommunity Champion
7 years ago

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")
  • vjnvinod's avatar
    vjnvinod
    Icon for Impactful Individual rankImpactful Individual
    7 years ago

    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's avatar
    Stachu
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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