Valuelist - Create own data to display in pie chart

[leoo]

Hi.

I'm trying to create a pie chart using the sales amounts from a sum-expression.

Now I have a sales table with diffrerent products. There are products who has been solid in one year, but not in the other. What I'm trying to do is show the percentage of the sales of the new products in comparison to the percentage of sales to the old products.

The total sales for the products  only sold in 2022 are 5 000 000.

The total sales for the products sold in both 2022 and 2021 are 10 000 000.

The total sales (both new and old products) are 15 000 000.

I've created a pie chart with the following dimension:

=valuelist ('NP', 'OP')

My measure is:

if(ValueList ('NP', 'OP') = 'NP',
Sum(({1<[SalesA]={"=sum({<Year={'2022'}>}[SalesQ])>0 and sum({<Year={'2021'}>}[SalesQ])=0"}>}[SalesA])),

if(ValueList ('NP', 'OP') = 'OP',
Sum(({1<[SalesA]={"=sum({<Year={'2022'}>}[SalesQ])>0 and sum({<Year={'2021'}>}[SalesQ])>0"}>}[SalesA])),
))

When I use this I get the total sales amount for each ValueList. E.g the total sales for NP is 15 000 000, when it should be 5 000 000, and the total sales for OP is 15 000 000 when it should be 10 000 000.

When I put them into a table just to see the results, the result I get is:

The result I want is:

I know the expressions work on their own, since when I put them in a table I get the right sales amounts. It just doesn't work when I'm trying to put them into a valuelist.

What am I doing wrong?

Thank you!

[leoo]

Hi.

I'm trying to create a pie chart using the sales amounts from a sum-expression.

Now I have a sales table with diffrerent products. There are products who has been solid in one year, but not in the other. What I'm trying to do is show the percentage of the sales of the new products in comparison to the percentage of sales to the old products.

The total sales for the products  only sold in 2022 are 5 000 000.

The total sales for the products sold in both 2022 and 2021 are 10 000 000.

The total sales (both new and old products) are 15 000 000.

I've created a pie chart with the following dimension:

=valuelist ('NP', 'OP')

My measure is:

if(ValueList ('NP', 'OP') = 'NP',
Sum(({1<[SalesA]={"=sum({<Year={'2022'}>}[SalesQ])>0 and sum({<Year={'2021'}>}[SalesQ])=0"}>}[SalesA])),

if(ValueList ('NP', 'OP') = 'OP',
Sum(({1<[SalesA]={"=sum({<Year={'2022'}>}[SalesQ])>0 and sum({<Year={'2021'}>}[SalesQ])>0"}>}[SalesA])),
))

When I use this I get the total sales amount for each ValueList. E.g the total sales for NP is 15 000 000, when it should be 5 000 000, and the total sales for OP is 15 000 000 when it should be 10 000 000.

When I put them into a table just to see the results, the result I get is:

The result I want is:

I know the expressions work on their own, since when I put them in a table I get the right sales amounts. It just doesn't work when I'm trying to put them into a valuelist.

What am I doing wrong?

Thank you!

[vinieme12]

If you want to exclude products from each year you need to filter on the products field not the sales field

NP =

 Sum({1<[Products]=e({<Year={"<2022"}>}[Products]),Year={"2022"}>}[SalesA] )   <<-- this will exclude all products that exists for prior to year 2022 , so you only evaluate data for new products

OP =

Sum({1<[Products]=e({<Year={'2022'}>}[Products]),Year={"2021"}>}[SalesA] ). <<-- exclude products  that exist in 2022

[leoo]

Hi Vinieme12

Thanks for taking your time.

Hmm ok. So if wanted to insert these into NP and OP how would I go about doing this?

I tried this:

if(ValueList ('NP', 'OP') = 'NP',
Sum({1<[Products]=e({<Year={"<2022"}>}[Products]),Year={"2022"}>}[SalesA] ) ,

Sum({1<[Products]=e({<Year={'2022'}>}[Products]),Year={"2021"}>}[SalesA] )
)

But all I get is "0" in the table. Thank you!

[vinieme12]

Does field [Products] actually exists in your model?, if yes how is it associated to your sales data?

can you post a screenshot of your data model

if(ValueList ('NP', 'OP') = 'NP',
Sum({1<[Products]=e({<Year={"<2022"}>}[Products]),Year={"2022"}>}[SalesA] ) ,

if(ValueList ('NP', 'OP') = 'OP',

Sum({1<[Products]=e({<Year={'2022'}>}[Products]),Year={"2021"}>}[SalesA] )
))

[leoo]

I have one big data file with all the sales data in it.

The field is called [Product] and not [Products], but I've changed it in the expression.

The dates (years, months etc), products and all data about the sales are in the same file.

[vinieme12 · Accepted solution]

works for me though

temp:
Load * inline [
Product,Year,Sales
A,2021,10
B,2021,10
C,2021,10
D,2021,10
A,2022,10
B,2022,10
E,2022,30
F,2022,30
];

Validation

NP = Only Product, E,F are new since they do not exist in year<2022

OP = Only Product C,D as they do not exist in 2022 but have sales in 2021

validation

Pie Chart:

Dimension = ValueList('Np','Op')

Measure = 

if(ValueList('Np','Op')='Np'
,Sum({1<Product=e({<Year={"<2022"}>}Product),Year={"2022"}>}Sales)
,if(ValueList('Np','Op')='Op'
,Sum({1<Product=e({<Year={'2022'}>}Product),Year={'2021'}>}Sales )
))