Filtering two tables and joining them into a calculated table without doing a merge in power query

[DanielJSP]

Hi,

I´m trying to compare two diferent "periods" that are in one table , in this table I have historical data so I need to filter diferent periods for base period (eg. May, Jun, Apr)  and end period (eg. Sep, Jun, May) from the dashboard and then I need to make some calculations that use a mix of information that changes depending on which periods where filtered as end and base.

I´m having trouble since I need to make this calculations according filtered periods (dynamic) but if I use functions such as crossjoin it is too slow and heavy. 

This is an example of main table where base and end periods are located:

And this what I need depending on filtered periods:

Calculation formula is = ((costbase/volbase)-(costend/volend))*volend

Thanks in advance for your help!

[V-lianl-msft]

Hi @DanielJSP ,

What is the logic of calculation here? It seems that it is not like the formula you provide.

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

[DanielJSP]

Hi Liang,

The logic in calculation is to make first the multiplication line by line and then sum it, the calculation works when only I have one product because the calculation is the same at product and cost account level. However, when I have more than one product, the calculation summarize all values and recalculates again as you can see below:

For example, I need to see the total calculation for all accounts, and in this case the result given by the measure is the sum of all cost/vol for base and end periods, and the sum of volume for end period and calculates the value with previous sums when I only need the sum line by line.

It is possible to do this?

Huge thanks in advance!

[V-lianl-msft]

Hi @DanielJSP ,

Unfortunately, only measure is dynamic in power bi, but i tried and can't get the results you want, especially the value of the total row.

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

[V-lianl-msft]

Hi @DanielJSP ,

Only the measure calculation is dynamic.

Based on your description, your ultimate goal is to calculate "((costbase / volbase) - (costend / volend)) * volend".

Try this measure:

Selection2 = 
VAR SelectedTag =
    VALUES ( 'Table (2)'[Period] )
VAR MinValue =
    MINX ( SelectedTag, MIN('Table (2)'[COST/VOL]) ) 
VAR MaxValue =
    MAXX ( SelectedTag,MAX('Table (2)'[COST/VOL]) ) 
VAR EndVol = MAXX(SelectedTag,MAX('Table (2)'[Volume]))
VAR Difference = (MaxValue - MinValue)*EndVol
VAR NumTagsSelected =
    DISTINCTCOUNT('Table (2)'[Period])
VAR Result =
    IF (
        NumTagsSelected = 2,
        Difference
    )
RETURN
    Result

Sample .pbix 

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

[DanielJSP]

Hi!

Thanks for your response, I tried your alternative but it is not working because the measure summarize all values calculating from totals instead of making a bottom up calculation and then summarizing all of each individual calc, since I have multiple combinations of products, periods, and accounts is important for me to be able of doing a calc from lowest levels.

Below an example:

[DanielJSP]

Do you if there´s a way to do this kind of calcs in Power BI?  Thanks!

[amitchandak]

@DanielJSP , Not sure I got it completely.

But see if this can help

https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/