cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rimgri Member
Member

SUMX with two tables

Hello,

 

Need some help with DAX.

I have two tables Historical Inventory data and Calendar table.

I need to calculate measure LS quantity. For the calculation I use data:

  • Active days - sums up Active column
    LS days - sums up  LS column
  • Trusted - formula is if LS days is less than Active days*0.8 returns zero, so it can't be used in calculations

 

LS quantity = 
VAR tbl= SUMMARIZE('Historical Inventory';'Historical Inventory'[KEY1]; "Active";[Active days];"LS"; [LS days]; "Sales"; SUM('Historical Inventory'[Sales]); "Trust"; [Trusted])
RETURN
    SUMX(FILTER(tbl; [Trust]=1); 
        ROUND(
             DIVIDE([Sales]; MAX([Active]-[LS days];1))*[LS days];1))

 

 

Everything works fine if I want statistical data for Item No., but when I want to see by the date it returns zero.

 

How should I modify DAX, to return correct LS quantity for both: Item No and date?

The issue to consider: this formula must be efficient, because it will be used in the big data table, so the latency must be as small as it can be.

Example file:

https://drive.google.com/file/d/14f_g9ht_hQFnYbVlemucBa1yfB_PnwS0/view?usp=sharing

 

 

2 REPLIES 2
Community Support Team
Community Support Team

Re: SUMX with two tables

Hi @rimgri ,

We can use the following DAX query to get trusted:

Trusted =
VAR Activedays =
    CALCULATE (
        COUNTROWS ( 'Historical Inventory' ),
        'Historical Inventory'[Active] = 1
    )
VAR LSdays =
    CALCULATE (
        COUNTROWS ( 'Historical Inventory' ),
        'Historical Inventory'[LS] = 1
    )
RETURN
    IF ( LSdays < Activedays * 0.8, 0, 1 )

Best Regards,

Teige

Highlighted
rimgri Member
Member

Re: SUMX with two tables

Hi @TeigeGao,

 

Thanks, for support, but maybe there was some misunderstanding, I don't have issues with calculating Trusted.

My problem is that I need to calculate LS quantity for items and for a date with one formula because for now, it calculates just for the item.

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 242 members 2,975 guests
Please welcome our newest community members: