Calculate with multiple criteria and different tables with measures

[PaulShlapa]

Hello everyone,

I have a database that consists of 3 Tables: Item Material, Data, Sales_all_Years

Item Material contains all items that are inside the database

Data contains list price and target price for those items

Sales_all_Years contains sales for those items in 2022 and 2023

I have a measure that was created inside the table Sales_all_Years, which intends to calculate change in sales between 2022 and 2023 in percent. I looks like this and work perfectly.

% Change Sales = 
VAR cur =
    CALCULATE ( 
        SUM ( 'Sales_all_Years'[Sales] ),
        FILTER ( 'Sales_all_Years', 'Sales_all_Years'[Year] = [Year max] ) )
VAR previous =
    CALCULATE (
        SUM ( 'Sales_all_Years'[Sales] ),
        FILTER ( 'Sales_all_Years', 'Sales_all_Years'[Year] = [Year max] - 1 ) )
RETURN
        IF(
        AND(
            NOT(ISBLANK(cur)), NOT(ISBLANK(previous))), 
            DIVIDE(cur - previous, previous), 
            BLANK()
            )

I have another measure that calculates percent deviation from ListPice and Regional Target Price, which works and looks like this

Percent deviation from Target Price Sales = 
if(isblank([ListPrice]),0,
if([ListPrice] = [Regional Target Price], 0, 
if([ListPrice] > [Regional Target Price], ([ListPrice] - [Regional Target Price])/[ListPrice],
if([ListPrice] < [Regional Target Price], (([ListPrice] - [Regional Target Price])/[ListPrice])))))

Now,

I am trying to modify the first measure and create 2 additional measures that

1) calculate % Change Sales only for items with List Price < Regional Target Price (Percent deviation from Target Price Sales < 0)

2)  calculate % Change Sales only for items with List Price > Regional Target Price (Percent deviation from Target Price Sales > 0)

Any help is appreciated

PBI dataset is added to the posted topic.

https://www.dropbox.com/scl/fi/206bdkzdja7qetjqd1n4g/PBIX_example.pbix?rlkey=eu7u00uyc409lxk51eh8bpt...  

https://buschvacuum-my.sharepoint.com/:u:/g/personal/pavlo_shlapa_busch_de/EZwg4G827U9EslBfMk8_ZJsBH... 

Sincerely,

Pavlo

[Anonymous]

Hi @PaulShlapa 

unfortunately that the pbix file can't be reached, when I open the link, it only give a blank page.

Maybe you can save the file and share by sharepoint so that we can help you better.

^{Best Regards,}

^{Zhengdong Xu}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

[PaulShlapa]

Hi @Anonymous , I have uploaded the File via new link, hope it works, if not please let me know.

Sincerely,

Pavlo