Creating a moving variable in a measure

[Anonymous]

Hi, 

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:

Broadcast

Digital 

Mail

Search

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

Example:

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?

[v-sihou-msft]

@Anonymous

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. 

Regards,

[Anonymous]

@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?

[AlbertoFerrari]

Hi,

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
http://www.sqlbi.com

[Anonymous]

@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!

Thanks!