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]

Hello @Bobass75 ,

Thanks for your feedback and clarification. I’ve tested this with a different sample dataset and updated the measures using SUMX. This ensures that not only the OPA level values are accurate, but also that the item Level subtotals and overall totals correctly reflect the sum of OPA level values, just as you mentioned.

Here’s what you’ll notice now.

1. Miara values are correctly grouped into <150k, 150-300, and >300buckets.

2. OPA level values are broken down accurately.

3. And most importantly item level and total rows now summarize based on those OPA level results, instead of recalculating from raw data.

Let me know if you'd like to review anything else or if further adjustments are needed.

Best regards,
Yugandhar

[Bobass75]

Hello

The idea is a bit different: calculating the sum (sales) of attributes other than OPA (opportunity) based on the sum of OPA. This means we first sum OPA, creating a range, and then assign the corresponding residual values of other attributes, such as ITM (item). (like in my example)

I'm struggling most with creating a measure that works without the OPA context in the table. It would be simpler to precalculate the sum/range of OPA in the table and assign a range ID to each row of the fact table, but this is supposed to work dynamically, so only DAX remains. 

I hope I explained my problem better 🙂 Thanks a lot !

[FBergamaschi]

Can you please republish this picture

indicating what you want to see in each cell? The arrows you draw there are confusing me

[Bobass75]

Of course
Final/desired state

[FBergamaschi · Accepted solution]

Done,

here is my solution

https://drive.google.com/drive/folders/1hzAYmnoNTT9-oEktmxwirq47gIqez9zF?usp=sharing

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

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

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

[Bobass75]

Thanks a lot !!!

[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