Having issues creating a dashboard with the ability to test a couple of scenarios

[DP2022]

Hi team

I currently have a dashboard set up on excel which will be described below. Essentially I want to set it up on Power BI and as currently set up in excel, there are a couple of variables I want to test to see how it impacts capacity and interest of a list of suppliers. I am also bringing in several other data sources into Power BI to enhance the supplier information - such as credit scores, financials, turnover, share price information, news. 

In excel Cells A2, A3 and A4 are names.

A2 = Project value

A3 = Duration (in years)

A4 = Average project value per year

B2 = a drop down list of values ranging from £100m to £2bn going up in increments of £50m.

B3 = a drop down list ranging from 1 to 20. 

B4 = B2/B3

Default figures are £400m and 1 year in cells B3 and B4 respectively.

Row 6 contains a dozen column header names.

A6 = company name

B6 = company registration number

C6 = latest annual turnover 

D6 = capped turnover (30%)

Formula in D7 is C7*0.3

E6 = Available single project spend per year.

F6 = Annual capacity

Formula in F7 is =IF(($B$4)>E7,"no capacity", "capacity")

G6 = project size limit

H6 = appetite for project size

Formula in H7 =IF(($B$2})<H7, "appetite", "no appetite")

I6 = turnover threshold

I7 has a formula = C7/($B$4)

You can see by adjusting the numbers in the dropdown lists in b2 and b3, they will cause the numbers within the table in columns F, H and I to change. 

So in excel it works fine, I can select different project duration years and project values to see which companies have appetite and capacity to work on projects. 

I am trying to recreate that in powerbi so that the table visual is there. I've put in calculated columns and measures for those formulas in row 7. 

However it's those drop downs in excel in b2 and b3 I can't recreate in powerbi, allowing the user to adjust those project values and years and to see the impact on the table visual. 

I've attempted to use what-if parameters under modeling for both, but they don't cause the table of data to change. Not sure if i have set it up incorrectly or Power BI can't do this. 

The aim is to get the table or additional graphs to change as you change the project scheme values and project duration (years). 

As mentioned earlier I've got other data sources connected to the power bi file such as credit scores for the companies, news articles, share prices, annual account data. This will feed into various visuals when a particular supplier is selected. 

It is just that part I have described above which I'm unable to replicate.

[Anonymous]

Hi @DP2022 ,

Based on your description, I created these data.

1. create two parameters.

2. created measure for dynamic calculation.

AverageProjectValuePerYear = 
[Project Value Value] / [Duration Value]

AnnualCapacity = 
VAR _num =
    VALUE (
        RIGHT (
            MAX ( 'Table'[Available Single Project Spend per Year] ),
            LEN ( MAX ( 'Table'[Available Single Project Spend per Year] ) ) - 1
        )
    )
RETURN
    IF ( [AverageProjectValuePerYear] > _num, "no capacity", "capacity" )

AppetiteForProjectSize = 
VAR _num =
    VALUE (
        RIGHT (
            MAX ( 'Table'[Project Size Limit] ),
            LEN ( MAX ( 'Table'[Project Size Limit] ) ) - 1
        )
    )
RETURN
    IF ( [Project Value Value] < _num, "appetite", "no appetite" )

TurnoverThreshold = 
VAR _num =
    VALUE (
        RIGHT (
            MAX ( 'Table'[Latest Annual Turnover] ),
            LEN ( MAX ( 'Table'[Latest Annual Turnover] ) ) - 1
        )
    )
RETURN
    _num / [AverageProjectValuePerYear]

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[DP2022 · Accepted solution]

Hi Clara

Thanks for taking the time and effort to look into this for me. 

When I was creating a table visual and including these 3 measures as columns, the table went blank with no information available on display:

AppetiteForProjectSize =

VAR _num =

    VALUE (

        RIGHT (

            MAX ( 'Table'[Project Size Limit] ),

            LEN ( MAX ( 'Table'[Project Size Limit] ) ) - 1

        )

    )

RETURN

    IF ( [Project Value Value] < _num, "appetite", "no appetite" )

-----------

AverageProjectValuePerYear =

[Project Value Value] / [Duration Value]

----------

TurnoverThreshold =

VAR _num =

    VALUE (

        RIGHT (

            MAX ( 'Table'[Latest Annual Turnover] ),

            LEN ( MAX ( 'Table'[Latest Annual Turnover] ) ) - 1

        )

    )

RETURN

    _num / [AverageProjectValuePerYear]

-----------

However, I amended these three measures to the following:

Annual Capacity Measure = IF([AverageProjectValuePerYear] > SUM('Table1'[Available Single Project Spend per Year]), "No capacity", "Has capacity")

Appetite Interest (calculated column used here) =
IF(
[Project Value Value] <= 'Table1'[Project Size Limit],
"Appetite confirmed",
"No appetite for this scheme size"
)

Turnover_Threshold_Ratio Measure = DIVIDE(SUM('Table1'[Latest Annual Turnover]), [AverageProjectValuePerYear])

This seems to have solved the issue which was occuring above. 

The two parameters, once adjusted, impact the table. 

Kind regards