Calculation of dynamic price differences between sites

[Anonymous]

Hello

I need to calculate price differences for a particular site weighted by kilos.

From the example that is attached, I require calculating the sum dif column of price differences for a site multiplied by the kilos.

The complexity is that the result of the dif column has to be dynamic for N sites. Sites can vary as different filter combinations are used.

The other thing that the price value I have is built in a single measure, which makes it difficult for me to perform the subtraction.

I'll appreciate your help.

In advance thank you.

Mario Ortiz Lazo

Prime-e

[v-shex-msft]

HI @Anonymous,

I'm not so sure for your table structure, can you please share some dummy data to test?

How to Get Your Question Answered Quickly 

In addition, you can try to use the following measure formula if it suitable for your requirement:

Measure =
VAR currkilos =
    CALCULATE (
        SUM ( table[value] ),
        FILTER ( table, [site] = "kilos" ),
        VALUES ( table[rango] ),
        VALUES ( table[sitio] )
    )
VAR summary =
    SUMMARIZE (
        FILTER ( table, [site] <> "kilos" && [site] <> "diff" ),
        [rango],
        [sitio],
        [site],
        "total", SUM ( table[value] )
    )
RETURN
    SUMX ( summary, currkilos - [total] ) * currkilos

Regards,

Xiaoxin Sheng

[Anonymous]

Thank you for the help, I have not yet been able to resolve it with the formula sent.

The table structure is as follows:

The price comes from the calculation of a measure from a table above.

Site	Kilos	Price	Range
Site 2	0,32498656	0,77329291	>32
Site 2	0,18944823	0,10221396	28-30
Site 2	0,32441327	0,77011824	26-28
Site 2	0,96794202	0,07210124	24-25
Site 2	0,96530041	0,01743075	22-24
Site 2	0,54640871	0,3242894	26-28
Site 2	0,30754043	0,01190993	22-24
Site 2	0,74980047	0,95539555	28-30
Site 2	0,37460999	0,49093909	24-25
Site 2	0,51586952	0,73825355	24-25
Site 3	0,68969797	0,1250866	24-25
Site 3	0,23950637	0,44562482	26-28
Site 3	0,8619103	0,27293254	28-30
Site 3	0,87682504	0,82933457	22-24
Site 3	0,16664113	0,77760338	24-25
Site 3	0,53439738	0,06658418	28-30
Site 3	0,01446091	0,61405017	26-28
Site 3	0,34323165	0,83421635	22-24
Site 3	0,99100039	0,72150281	24-25
Site 3	0,15734091	0,80886052	28-30
Site 1	0,0780961	0,56291788	28-30
Site 1	0,78275947	0,02478303	24-25
Site 1	0,83266673	0,69460931	26-28
Site 1	0,46107876	0,28689272	22-24
Site 1	0,7934675	0,68517226	>32
Site 1	0,51215635	0,03011704	26-28
Site 1	0,2473833	0,67200609	22-24
Site 1	0,84154261	0,44531639	24-25
Site 1	0,28031881	0,93173736	>32
Site 1	0,1953479	0,45932768	28-30

Best regards

Mario Ortiz

[v-shex-msft]

Hello @Mortiz,

You can refer to the following steps to achieve your requirements.

1. Make an un pivoted column in the Pirce and Kilos fields.

Un pivoted columns (power query)

2. Create an array visual with the range as row, site, type (un pivoted attribute field) as column.

3. Type a measurement formula to use its row content to calculate the result and use it in the array value fields.

Measure = 
VAR currKilo =
    CALCULATE (
        SUM ( T2[Value] ),
        FILTER ( ALLSELECTED ( T2 ), T2[Type] = "Kilos" ),
        VALUES ( T2[Range] ),
        VALUES ( T2[Site] )
    )
VAR price =
    CALCULATE (
        SUM ( T2[Value] ),
        FILTER ( ALLSELECTED ( T2 ), T2[Type] = "Price" ),
        VALUES ( T2[Range] ),
        VALUES ( T2[Site] )
    )
VAR siteCount =
    COUNTROWS ( EXCEPT ( ALLSELECTED ( T2[Site] ), VALUES ( T2[Site] ) ) )
VAR totalPrice =
    CALCULATE (
        SUM ( T2[Value] ),
        FILTER (
            ALLSELECTED ( T2 ),
            NOT ( [Site] IN VALUES ( T2[Site] ) )
                && [Type] = "Price"
        ),
        VALUES ( T2[Range] )
    )
RETURN
    IF (
        ISFILTERED ( T2[Type] ),
        SUM ( T2[Value] ),
        ( price * siteCount - totalPrice ) * currKilo
    )

4. Add a sort table to extract the range value and add 'order' fields, then set the order fields as the 'sort by column' range field and use it to replace the array range field.

Sort by column in Power BI Desktop Custom sorting in Power BI

Sort = 
ADDCOLUMNS (
    VALUES ( T2[Range] ),
    "Order",
    VAR result =
        PATHITEM ( SUBSTITUTE ( [Range], "-", "|" ), 1 )
    RETURN
        IF (
            SEARCH ( ">", result, 1, -1 ) > 0,
            RIGHT ( result, LEN ( result ) - 1 ),
            result
        )
)

Design and result of the matrix:

BTW, I also attached the sample file below if you are confused about these steps.

Best regards

Xiaoxin Sheng