Dynamic data vizualization filter line and stacked column chart

[renatopnovaes]

CODE PROD	COST	SALE	QUANTITY	REGION	DATE
123	10	15	3	CALIFORNIA	01/12/2021
125	30	45	1	NEW YORK	02/12/2021
350	50	75	2	TEXAS	01/12/2021

Primary	Secondary	Descript	Amount	Date	REGION
5-Operating Expense	5.3-Food	Dinner John	100	01/12/2021	CALIFORNIA
3-Taxes	3.1-Cofins	Cofins New York	5000	02/12/2021	NEW YORK
6-Delivery Expenses	6.1- Vehicle Maintenance	truck weld	1000	03/12/2021	GENERAL
8-Logistic	8.1- Fuel	fuel truck	3000	04/12/2021	LOGISTIC
4-Person Expenses	4.1- Prizes	seller Peter Prize	3.000	05/12/2021	TEXAS

I'm trying to create a line and stacked column chart with Sales, Product Cost, Expenses and Net Income. The Sales, Cost of Product data is taken from the Sales Table. The expenses are removed from the expense table. In the Sales table I have 6 columns, Date, Product, Quantity, sales value, cost value and sales region. In the Expenses Table I have 6 columns as well. Primary Expense, Secondary Expense, Description, Amount, Date and Region.

The net income is: Sum((Table 1[Sale]) - Sum(Table1[Cost]) - Sum(Table2[Amount]. However, the profit for each region is different because the values ​​of Table2[Region]= General and Logistic are diluted in the regions with the following constant.

Constant = Calculate(Sum(Table1[Quantity]), Table1[Region] = " X ") / Sum(Table1[Quantity]) X corresponds to the chosen region.

For example the net income of the New York region is = Calculate(Table1[SALES - COSTS]), Region = "New York") - (Calculate(Sum(Table2[Amount], Region="New York")) + Calculate (Table1[SALES - COSTS]), Region = "LOGISTIC", "GENERAL")* Constant Area New York )

{{this measure for region above is only a example}}

The idea was to create an area filter that, when clicking on the slicer, would adjust to the values ​​of that region. However, the net profit never follows the information because it is the result of a multiplication by a constant. I created a region dimension via Values=Table1 to create a filter template but I still can't show it the way I need it. I did a summarize of the two tables, but I still can't insert the amount of expenses for each region according to the calculation. But I can't filter the way I want.

I also tried to create two measures to adjust the constant. I created a Calculate with removeFilters which would be the total boxes sold and a sum of boxes. Thinking that the filter would apply only to one of these measurements when extracting a dynamic constant.

Can any expert friends help me get rid of this problem?

[renatopnovaes]

The constant comes from the value sold in the region during the month divided by the total sold in the month. Expenses are the region values more the LOGISTIC and GENERAL values ​​multiplied by this constant added to the values ​​that are already from the region itself.

. The problem is that when I bring the information I can't really slice it according to the region.  

I will rephrase my question.

[v-yanjiang-msft]

Hi @renatopnovaes ,

In my understanding, is your main problem that the constant measure don’t differ according to the region slicer?

I create a sample according to your data, but I haven’t reproduced your problem.

Here’s my formula:

Constant =
VAR _Sub=MAX('Sales'[Quantity])
VAR _Sum=CALCULATE(SUM(Sales[Quantity]),REMOVEFILTERS(Sales[Region]))
RETURN
DIVIDE(_Sub,_Sum)

Net income =
(
    CALCULATE ( MAX ( 'Sales'[Sale] ) - MAX ( 'Sales'[Prod cost] ) )
        - CALCULATE ( SUM ( 'Expenses'[Amount] ) )
        + CALCULATE (
            MAX ( 'Sales'[Sale] ) - MAX ( 'Sales'[Prod cost] ),
            REMOVEFILTERS ( Sales[Region] ),
            'Expenses'[Region] = "LOGISTIC"
                || 'Expenses'[Region] = "GENERAL"
        )
) * 'Sales'[Constant]

Additionally, I’m not very clear about “General and Logistic are diluted in the regions with the following constant”, as in the formula “Calculate(Table1[SALES - COSTS]), Region = "New York") - (Calculate(Sum(Table2[Amount], Region="New York")) ” each value are belong to New York, has no matter with LOGISTIC and GENERAL, and in your formula “ Calculate (Table1[SALES - COSTS]), Region = ("LOGISTIC", "GENERAL")” has no value, as the two tables are related with Region column, but there is no LOGISTIC and GENERAL region in table1.
I attach my sample below, you can modify it and post again.

Best Regards,
Community Support Team _ kalyj

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