Showing results for 
Search instead for 
Did you mean: 

Creating a moving variable in a measure



I have a table that is manually updated by a business user. The number that is being updated should match a Total number within a differrent sales table. 


The table has multiple channels:








> "Search" and "Mail" should be considered as an "Other" channel. 


That "Other" channel should also contain the calculation that allows the total count of channel sales to equal the number that is being manually updated by the business user. 





User inputs number = 870


Channel          Sales

Broadcast       500

Digital             200

Other              150 (Mail = 100; Search = 50)


Total               850


I need to create a measure that allocates [870-850] = "20" sales (or whatever the difference is) to the "Other" category


Any help?




Re: Creating a moving variable in a measure



No, it's not possible to use a measure to have user input number minus total to allocate it into a Category. Even you can have the user input values as Query Parameter and update the source table, but it will execute this update every time once you load this dataset which is a very bad practice. 



Re: Creating a moving variable in a measure

@v-sihou-msft THANK YOU!


I understand where you are coming from with this, and I agree.


But could you explain it a bit more? My colleagues are understanding why this is bad practice, and I've already initially told them what you 1st posted. 



Could you help with any options?

Re: Creating a moving variable in a measure



If the user inputs the information in some table that you later load into a PBI file, you can solve the problem with some basic coding. I would love to help you more on this, but I would need some sample data (just a sample of the sales table and the user input). With that, and the aid of some basic DAX, the scenario can be solved.


Of course, this will work if the data is inside Power BI. If, on the other hand, you want the data to be dymaically selected by the user, then you will need a parameter table and some more advanced DAX code.


But, first, let us start with the sample data, then we will go deeper on this.


Alberto Ferrari

Alberto Ferrari - SQLBI

Re: Creating a moving variable in a measure

@AlbertoFerrari I may have found a way around having to do this, in specific, for this report; but I will definitely come back to this question and understand the way of doing this!





Helpful resources

Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors