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

 

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

 

  • 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

    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
  • 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