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
vitexo87
Post Prodigy
Post Prodigy

How to create an input object and a variable?

I need to make available in a dashboard where there is the possibility of the user imputing information and this information will interact with a measure and that finally this measurement will be displayed in graphical, for example:

 

I have a measure where I present the value of the sales target for that year based on what was sold in the previous year, for example, 2016 was sold 1 million and the target for 2017 is 1.5 sales, in this case an increment Of 50%, but this increment is not a fixed value and who determines this is the user, today he wants to apply a goal of 50% tomorrow he may want to apply a goal of 10%, and to be doing it in the database is not feasible .

 

Is there any object in power bi where the user can enter the information and that information be stored in a variable and that variable be used in a measure?

1 ACCEPTED SOLUTION
Sean
Community Champion
Community Champion

@vitexo87

Its seems you have a Secondary Y-Axis for the line (its on the right side of the chart in the second picture you posted)

 

You have to turn it off if you want the line to stay above!

 

Secondary Y-Axis.gif

View solution in original post

11 REPLIES 11
juchen
Employee
Employee

The alternative way I'm usign is "What if parameter"+ "Measurement".
Below is my example: User manually input currency rate(USD- Taiwan Dollor), and the USD list price will be changed accordinly.
https://docs.microsoft.com/en-us/power-bi/desktop-what-if 

what-if parameter.png

 

afryer
New Member

Seems What if Parameters can solve this problem relatively cleanly if folks are still looking for a solution (as I was).

Sean
Community Champion
Community Champion

Currently there's no way to truly get user input.

 

There's an Idea about it though

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/9414921-input-fields

 

You can kind of simulate this with a parameter table and Slicers but it will not be really user input (more like user choice)

 

EDIT: Okay I found the link to my previous post showing the paramter table option...

https://community.powerbi.com/t5/Desktop/Dynamic-Increase/m-p/37122#U37122

Disconnected Slicers.png

 

@Sean

 

Okay, I figured I did not have this feature even if I do with the filter features as an employee? Would I apply the filter field with the percentage in measure?


I believe I would have to create a field with the possible percentages the user would use.

Sean
Community Champion
Community Champion

@Sean

 

Thanks for the link, I am trying to apply something similar in power bi, but it is not working as expected, would you have some example of this link applied in power bi?

Sean
Community Champion
Community Champion

@vitexo87

To see an example go to the bottom of the page at the above link where it says Downloads

 

Download the Excel 2013 zip files - extract all

 

Then Open PBI - File - Import - Excel Workbook Contents - Select ParametersTable - Multiple - Excel 2013.xlsx - Open

 

click Start on the warning "We don't work directly with Excel... blah blah..." - Copy Data - Migration Completed - Close

 

Save the File as pbix - you may have to close and open it again

 

Now create a Matrix - place Product in the Rows and SalesAmount and DiscountedSalesAmount in the Values (Sales table)

 

then create a Slicer with the field from the Discounts table - select any value from the slicer and see how the Measure reacts!

 

Hope this helps! Smiley Happy

 

 

 

 

 

@Sean

I loaded the tables but I did not find a way to relate them to the .xls data and even then I'm trying to adapt the following measure to the table that has the% information, but I'm not getting any tips

 

Meta Liquidação2 =
SUMX (
FILTER ( ALL ( Fatos ); Fatos[Ano_ID] = MAX ( Fatos[Ano_ID] ) - 1 );
Fatos[Vl_Liquidado] - Fatos[Vl_Devolvido]
+ Fatos[Vl_DevolucaoCancelada]
- Fatos[Vl_LiquidacaoCancelada]

* (TabelaMeta[%]))

 

 

 

 

Untitled.png

Sean
Community Champion
Community Champion

@vitexo87

TabelaMeta should not be related to any other table! Then create a Slicer with the TabelaMeta[%] column

in the Measure below you'll multiply times the other column TabelaMeta[%Valor]

 

So if anything in the Slicer is selected - you'll multiply the result of SUMX times the value selected in the Slicer

if nothing is selected you'll just get the result of SUMX

 

 

Meta Liquidação2 =
IF (
    HASONEVALUE ( TabelaMeta[%] );
    SUMX (
        FILTER ( ALL ( Fatos ); Fatos[Ano_ID] = MAX ( Fatos[Ano_ID] ) - 1 );
        Fatos[Vl_Liquidado] - Fatos[Vl_Devolvido]
            + Fatos[Vl_DevolucaoCancelada]
            - Fatos[Vl_LiquidacaoCancelada]
    )
        * VALUES ( TabelaMeta[%Valor] );    // this will only calculate the %
    SUMX (
        FILTER ( ALL ( Fatos ); Fatos[Ano_ID] = MAX ( Fatos[Ano_ID] ) - 1 );
        Fatos[Vl_Liquidado] - Fatos[Vl_Devolvido]
            + Fatos[Vl_DevolucaoCancelada]
            - Fatos[Vl_LiquidacaoCancelada]
    )
)

 

You may have to adjust this part

 

* VALUES ( TabelaMeta[%Valor] );            <= calculates only the %

*
( 1 - VALUES ( TabelaMeta[%Valor] ) ); <= discount applied

*
( 1 + VALUES ( TabelaMeta[%Valor] ) ); <= premium added

 

Hope this helps! Smiley Happy

 

@Sean

Thanks for the help, it worked, but only one error occurs when I select the values of 20% or more, the graph line is below the real value, and it should stay above as the other values of 15% or less, the Non-graphic value when selected is correct but the line display is incorrect.

 

Untitled.png

 

Untitled2.png

Sean
Community Champion
Community Champion

@vitexo87

Its seems you have a Secondary Y-Axis for the line (its on the right side of the chart in the second picture you posted)

 

You have to turn it off if you want the line to stay above!

 

Secondary Y-Axis.gif

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.