cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
benjaminlenglet
Frequent Visitor

Measure from non related Table Always null

Hi Everyone, I hope somebdy will be able to help me, i've been reading hundreds of forum topics all night long, and im desperate right now 😕 

 

Here is my schema of Data, 

Capture_LI.jpg

Here is the content of Fact Promotion

 

Date KeyProduct LCBO KeyProduct bridge keybottle sizeCases 9L 
20200101111BBB7500.833
20200101222CCC7500.444
20200101333DDD7500.856

 

here is the content of my table Finance

 

Date KeyProduct LCBO KeyProduct bridge key
Shipment Actual Cost Of Goods Sold
2020222BBB1.670333
2020222AAA22.499000
2020333DDD0.563226

 

The idea here is to create a measure called

COGS_TOTAL = Finance[shipment cost of goods] * Fact promotion[Cases 9L] 

 

 

 

 

 

 

COGS_TOTAL = [sum9l] * [total Shipment Actual Cost Of Goods Sold]

 

 

 

 

 

 

with : 

sum9l = Sum(Fact promotion[Cases 9L]) 

total Shipment Actual Cost Of Goods Sold = sum(Shipment Actual Cost Of Goods Sold)

 

benjaminlenglet_5-1621069985648.png

The total of COGS_TOTAL is correct but I want to have it as a sum of all the rows above. which mean 553385589

So I readed about 30 topic of this forum to understand this is a total measure issue  and that i need to do a simple SUMX

 

BUT then when I do my 

 

 

 

 

 

 

SUMX_COGS_TOTAL = SUMX(Calendars, [sum9l] * [total Shipment Actual Cost Of Goods Sold])

 

 

 

 

 

 

 

 

benjaminlenglet_7-1621070095610.png

The result is completely different but at least is now the sum of every rows, BUT let me add the product dimensions right now : 

benjaminlenglet_8-1621070396965.png

Result is now completely WRONG. 

 

So after reading many topics, I understand that if i want to slice by many dimensions, I need to use summarize function with addcolumn somehting like : 

 

 

 

 

 

 

SUMMARIZE_COGS_TOTAL = SUMX (
    ADDCOLUMNS (
        SUMMARIZE (
            'Facts Promotion',
            'Facts Promotion'[Cases 9L],
            'Product LCBO'[LCBO SKU No],
            Calendars[Date Key],
           'Financial'[Shipment Actual Cost Of Goods Sold]
        ),
        "COGS", [sum9l] * [total Shipment Actual Cost Of Goods Sold]
    ),
    [COGS]
)

 

 

 

 

 

 

 

But i have errors : 

Which is normal because there is no direct relationship between Fact promotion table & Financials. 

benjaminlenglet_10-1621070812790.png

 

Im sorry I cannot share the PBIX because there is sensible information, But i will do my best to give you all informations. 

I tried my best to read everything on this forum, but my issue seems to be more complex than expected. 

 

 

7 REPLIES 7
mahoneypat
Super User IV
Super User IV

Your example data does suggest the DateKey granularity is not the same, but maybe that is just a typo.  In any case, because you have two fact tables, you are not able to use SUMMARIZE on just one of them.  You could try a CROSSJOIN expression like this:

 

COGS =
SUMX (
    CROSSJOIN ( VALUES ( Calendars[DateKey] )VALUES ( Product[LCBO SKU] ) ),
    [sum91] * [total shipment Cost of Goods Sold]
)

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Hi @mahoneypat  thank you again for helping me  🙂 

I think i'm going to the right direction, 

 

tempsnip.png

 

Here is the result of my calculation: 

OLD_COGS here is  a simple : 

[sum9l] * [total shipment Cost of Goods Sold]

 

CrossjoinCOGS is : 

So basically I put all related table from both fact tables in the crossjoin section, is that correct ? 

CrossjoinCOGS
    = SUMX (
        CROSSJOIN (
            VALUES ( 'Product Master'[Product Key] ),
            VALUES ( 'Product LCBO'[Lcbo_Product_Key] ),
            VALUES ( 'Channel Finance'[Channel Code] ),
            VALUES ( Calendars[Date Key] )
        ),
        [sum91] * [total shipment Cost of Goods Sold]
    )

 

And summarizeCOGS is 

Basically I put all related column from both fact tables, same questions is that correct ? 

SummarizeCOGS = 
SUMX (
    ADDCOLUMNS (
        SUMMARIZE (
            'Financials',
            'Product LCBO'[LCBO SKU No],
            'Channel Finance'[Channel Code],
            'Product Master'[Product Key],
            'Calendars'[Date Key]
        ),
        "COGS", [sum91] * [total shipment Cost of Goods Sold]
    ),
    [COGS]
)

 

I'm happy, because i feel like I have the same result with both calculation, however an other question come to my mind : 

  1. Is this result being lower than the initial one is because we take all values that we have in common ? just like a innerjoin in SQL right ? 
  2. In terms of performance, what calculation should I use ? 

Thank you so much again @mahoneypat & @PaulDBrown for answering 

I wasn't sure if your two measures use columns in both fact tables or not.  If the SUMMARIZE one gives the expected values, I would expect it to be more performant.  However, since you have both written, I would try them separately in your visual and view the results with Performance Analyzer (or just try them both out qualitatively) and see which one is faster.

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


PaulDBrown
Super User II
Super User II

Where is the LCBO Promo Period field coming from?

Also do the date keys in your Finance and Fact promotion have the same granularity (it seems the Finance table has a year granularity while the Fact Promotion table has a day granularity).

Also make sure that any fields in the visual come from the dimension tables (not the fact tables)





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Thank you @PaulDBrown for taking the time to replying me. 

 

Where is the LCBO Promo Period field coming from?

LCBO Promo Period  is coming from my table Calendar (which is linked to both fact tables (financial & fact promotion)) 

 

Also do the date keys in your Finance and Fact promotion have the same granularity (it seems the Finance table has a year granularity while the Fact Promotion table has a day granularity).

No, both fact tables has the same granularity (date key) and have same dimensions linked to them (Calendar / Product LCBO / product Bridge )

 

Also make sure that any fields in the visual come from the dimension tables (not the fact tables)

I dont get it ? can you please explain 

 

 

 

 

 

 

@benjaminlenglet 

Perhaps try:

_measure = SUMX(

SUMMARIZE(calendar, Calendar [LCBO Promo Period], "_COGS", [sum9l] * [total Shipment Actual Cost Of Goods Sold]),

[_COGS])





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






benjaminlenglet
Frequent Visitor

I've Also tried to read this post which look like the same issue finally as me, but no one answer too 😕 

SUMX with multiple tables - Microsoft Power BI Community

Should i create a temporary table to store the values ? trying to link my fact promotion with financials table ? 

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors