Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Guys,
I cannot work out how to calculate the following.
I have the following:
County Table.
Sales Table.
Date Table
Product Table
I've got a summary tab where my matrix will show the total sales and profit etc by country and by year.
Like the below.
Country | SALES 2018 | SALES 2017 | SALES 2016 |
USA | 36,152 | 27,372 | 27,239 |
UK | 23,962 | 22,489 | 19,129 |
SWE | 27,408 | 27,085 | 22,061 |
NOR | 12,284 | 9,267 | 0 |
FIN | 9,663 | 10,290 | 10,290 |
POL | 29,733 | 26,933 | 25,270 |
BRA | 19,082 | 14,592 | 0 |
FRA | 12,582 | 10,197 | 8,811 |
POR | 13,783 | 11,684 | 8,469 |
ITA | 18,010 | 14,235 | 11,723 |
So to the issue.
Where a country has not got any sales for 2016, i wish to replace it with last year i.e. 2017.
However i do not wish to do this for all the countries, because when i look at the product view it will show sales replication for all the countries.
The below does not work for me (only for a Country overview but not down to product by country.
IF(
CALCULATE (
[Sales 2018],
DATEADD ( 'Date'[Date], - 728 , DAY ))
= BLANK (),
[Sales 2017],
[Sales 2018])
I want to select Brazil and Norway, and tell the calculation that replace the 2016 figures for those 2 countries with 2017 figures.
Something like :
IF(
CALCULATE (
[Sales 2018], 'Countries'[Country] = "NOR" || "BRA",
DATEADD ( 'Date'[Date], - 728 , DAY ))
= BLANK (),
[Sales 2017],
[Sales 2018])
Hi @PeterL1,
Can you please share some detail information to help us clarify your scenario? (such as sample data, pbix file, expected result,...)
Regards,
Xiaoxin Sheng
Hi @v-shex-msft
Below image,
Where Brazil and Norway in the left most column (summary) has no sales for either 2017 or 2016, i want to use the full sales years data instead.
E.g. Brazil's total sales for 2016 are 0, so want to use the 2017 sales, but only for Brazil.
Norway has no sales for 2017, so i want to use the 2018 sales, but only for Norway, etc.
If i would calculate,
= IF (Sales 2016 = BLANK(), 2017 Sales, Sales 2016)
It will then duplicate the sales for the other Countries that has blank sales for 2016 in the right most table where my products are, which is incorrect.
Also see link to the PBIX file with the information as well.
https://www.dropbox.com/s/13uc1wrwhqlp9bz/Blank%20Sales%20PBIX.pbix?dl=0
Thanks
Peter
Hi @PeterL1,
You can refer to below formula to get replaced sell value.
Sales 2016 = VAR temp = CALCULATE ( SUM ( Sales[SELL NET] ), 'Date'[Year] = 2016, VALUES ( Products[DESCRIPTION] ) ) RETURN IF ( temp <> 0 && [Sales 2018] <> BLANK (), temp, [Sales 2017] ) Sales 2017 = VAR temp = CALCULATE ( SUM ( Sales[SELL NET] ), 'Date'[Year] = 2017, VALUES ( Products[DESCRIPTION] ) ) RETURN IF ( temp <> 0 && [Sales 2018] <> BLANK (), temp, [Sales 2018] ) Sales 2018 = CALCULATE ( SUM ( Sales[SELL NET] ), 'Date'[Year] = 2018, VALUES ( Products[DESCRIPTION] ) )
Result:
Regards,
Xiaoxin Sheng
Hi @v-shex-msft,
Not quite what i need,
What you have written will bring out, all blank sales, on product description level, where the sale is blank to return the previous year values.
What i need:
If the Total sales for a country for a year is blank, replace the zero values for that country only with the previous year values.
For Example.
Where USA has 0 sales on a product called Pine Cone for 2016, but has sales for other products in 2016, i want the Pine Cone to say 0.
Where Brazil has no sales at all for 2016, i want to replicate the sales for 2017 to 2016.
So only replicate if the full year is blank.
I hope this makes sense.
Thanks
Peter
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |