Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

What if parameter & date table

Hello! Would appreciate some input here. Suppose my scenario is looking at total annual sales by year. To get the total sales for each year, i would multiply cost by items sold.

For 2016-2019 I’m wanting to create 2 what if scenarios - price x and items sold x for each year.

For 2020 through 2022, I’m wanting to create the same whatif parameters, as well as a trend %.

I know how to create whatif parameters - my challenge is having the parameters for each year all in the same table to create a line graph. Any tips?
1 ACCEPTED SOLUTION

You could try something like this. For each of you sets of parameters(number of users, cost pr user), createa a measure like this

CostParameter =
SWITCH (
    TRUE ();
    SELECTEDVALUE ( DimDate[Year] ) = 2016; CostPrUser2016[CostPrUser2016 Value];
    SELECTEDVALUE ( DimDate[Year] ) = 2017; CostPrUser2017[CostPrUser2017 Value];
    SELECTEDVALUE ( DimDate[Year] ) = 2018; CostPrUser2018[CostPrUser2018 Value];
    1
)

Then create a measure like this

Measure =
SUM ( Table[Number of users ] ) * [UserParameter]
    * SUM ( Table[Cost pr unit] ) * [CostParameter]

Add year to the x-axis, and measure as value. If you need a trend line, you can add this through the analytics options of the chart.

cheers,

View solution in original post

7 REPLIES 7
v-diye-msft
Community Support
Community Support

Hi @Anonymous 

 

Sorry not very clear from you post what you are looking for. Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
Anonymous
Not applicable

@v-diye-msft @sturlaws  please see my latest response

You could try something like this. For each of you sets of parameters(number of users, cost pr user), createa a measure like this

CostParameter =
SWITCH (
    TRUE ();
    SELECTEDVALUE ( DimDate[Year] ) = 2016; CostPrUser2016[CostPrUser2016 Value];
    SELECTEDVALUE ( DimDate[Year] ) = 2017; CostPrUser2017[CostPrUser2017 Value];
    SELECTEDVALUE ( DimDate[Year] ) = 2018; CostPrUser2018[CostPrUser2018 Value];
    1
)

Then create a measure like this

Measure =
SUM ( Table[Number of users ] ) * [UserParameter]
    * SUM ( Table[Cost pr unit] ) * [CostParameter]

Add year to the x-axis, and measure as value. If you need a trend line, you can add this through the analytics options of the chart.

cheers,

Anonymous
Not applicable

brings back a blank

When you set up you parameter, was the first step 0? If you look at the tables generated by the parameter, there are 2 fields. Look at the one which does not end with value. It will say something Generateseries(0;20;1). The first value is the start value. By default, the when setting a new parameter, this value is 0.

If this does not solve you issue, please post the code of you measure

Anonymous
Not applicable

 

This is what the page would look like... I assume that for each year, there would be 2 parameters (example, for 2017 there would be 1 parameter for # of Users and 1 parameter for $ per user.

Year    # Of Users          $ Per User                   Total Cost

2017User InputUser Input*=#ofusers X costperunit*
2018User InputUser Input*=#ofusers X costperunit*
2019User InputUser Input*=#ofusers X costperunit*

 

The client would be able to manually input into the fields above

 

the end result would look like this

 

Year                       # Of Users        $Per User       Total Cost

2017550250
20181055550
2019860480

 

 

 

The visual would look like this exmaple1.PNG

sturlaws
Resident Rockstar
Resident Rockstar

Hi,

 

it is not clear to me what your data look like, or how you want you visualization to look like. Do you want the line chart years on the x-axis, or do you want the actual parameter values?

 

Perhaps you could create a mock-up of your desired result, and also create a sample dataset?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.