Forum Discussion

vjnvinod's avatar
vjnvinod
Impactful Individual
7 years ago
Solved

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

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")

11 Replies

  • Stachu's avatar
    Stachu
    Community Champion
    7 years ago

    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)