How to fix range measure calculation

[Bobass75]

The goal for the measure is to calculate sales value ranges based on OPA_ID in the current context.

Range < 150k Sales Invoices Amount =

VAR FilteredOPA =

    FILTER (

               ADDCOLUMNS (

                SUMMARIZE ( 'Fact Sales Invoices', 'Dimension Opportunity Attributes'[OPA ID] ),

                "Sales", 'Fact Sales Invoices'[Sales Invoices Amount NOK]                      

            )

        ,

        [Sales] < 150000

    )

VAR selected =

    SELECTCOLUMNS ( FilteredOPA, 'Dimension Opportunity Attributes'[OPA ID] )

VAR Table_with_lineage =

    TREATAS ( selected, 'Dimension Opportunity Attributes'[OPA ID] )

RETURN

    CALCULATE ( 'Fact Sales Invoices'[Sales Invoices Amount], Table_with_lineage )

The problem is that the measure calculates incorrect values in rows if the context is on a product (ITM_ID)- an example below.

	measure <150k	measure >=150-500k	measure >=500k<1000k
OPA_ID 1	146
ITM1	100
ITM2	46
OPA_ID 2			550
ITM3	60
ITM4	80
ITM5		270
ITM6	140

How can I modify the measure so that the ITM3-ITM6 values appear in the third column, where sum is 550 (sum of OPA=550)?

Thank you in advance for your help.

[Bobass75]

Thanks - it will be better if I attach an example 😁 - so 
Measures calculate correctly only OPA levels summary - I want  to have also correct ITM level summary (and Totals) based on OPA like  :

Thanks a lot !!

https://drive.google.com/file/d/1hLcXEbaiyvjvBw_tajke4uschQUNVQz0/view?usp=sharing 

[V-yubandi-msft]

Hi @Bobass75 ,

I’ve replicated your requirement using sample data and made some adjustments to the original measure to ensure it functions correctly.

FYI:

I’ve attached the .pbix file for your review. Please let me know if it meets your needs or if you’d like any changes.

Best regards,

Yugandhar,

[FBergamaschi]

Hoping to have understood what yo uare willing to get

Range < 150k Sales Invoices Amount =

VAR FilteredOPA =

    FILTER (

               ADDCOLUMNS (

                SUMMARIZE ( 'Fact Sales Invoices', 'Dimension Opportunity Attributes'[OPA ID] ),

                "Sales", 'Fact Sales Invoices'[Sales Invoices Amount NOK]                      

            )

        ,

        [Sales] < 150000

    )

VAR selected =

    SELECTCOLUMNS ( FilteredOPA, 'Dimension Opportunity Attributes'[OPA ID] )

VAR Table_with_lineage =

    TREATAS ( selected, 'Dimension Opportunity Attributes'[OPA ID] )

RETURN

    CALCULATE ( 'Fact Sales Invoices'[Sales Invoices Amount], Table_with_lineage, REMOVEFILTERS ( Product[ProductNo] )

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI