count rows of calculated column

[lorenzolancio]

Hello everybody 🙂

this table shows the turnover of the 2017 and 2018 grouped by costumer id. This table is not the data source, but is the result of a big transactional database.

In this table i have:

1st column: difference of percentage between the turnover of 2017 in column3 and of 2018 in column4.

2nd column: costumer code

I would like to find a new measure for counting the rows filtered of the first column.

I want to know how many costumer has the green, blue, yellow and red cell in the first column (the colors are the result of a conditional formatting of the value of the cell).

green: more than 0%

blue: blank

yellow: between 0 and -100%

red: -100%

I'm not good in DAX 🙂

i Hopre that someone can help me.

Thank you,

Bye!

Lorenzo

[v-qiuyu-msft]

Hi @lorenzolancio,

You can create measures below: 

GreenCount = CALCULATE(COUNT('Table'[Difference of percentage]),FILTER(ALLSELECTED('Table'),'DimSalesTerritory'[Difference of percentage]>0))

BlueCount = CALCULATE(COUNT('Table'[Difference of percentage]),FILTER(ALLSELECTED('Table'),'DimSalesTerritory'[Difference of percentage]= Blank()))

YellowCount = CALCULATE(COUNT('Table'[Difference of percentage]),FILTER(ALLSELECTED('Table'),'DimSalesTerritory'[Difference of percentage]>-1 && 'DimSalesTerritory'[Difference of percentage] <0 ))

RedCount = CALCULATE(COUNT('Table'[Difference of percentage]),FILTER(ALLSELECTED('Table'),'DimSalesTerritory'[Difference of percentage]= -1))

Best Regards,
Qiuyun Yu 

[lorenzolancio]

Hi @v-qiuyu-msft,

Thak you very much for your answer,

but it doesn't works because the column "Difference of Percentage" is a calculated column.

When i try to put that expression the error message is "I can't find Difference of Percetage column, it doesn't exist".

I think that we sholud try to create that kind of calculation in DAX espression, and then counting the rows as we said.

Have you any idea how we can do it?

Thank you very much,

Bye!

Lorenzo

[v-qiuyu-msft]

Hi @lorenzolancio,

In my sample DAX, the column "Difference of Percentage" is the "1st column: difference of percentage between the turnover of 2017 in column3 and of 2018 in column4." you mentioned in your initial post. Please replace it with your actual column name. 

If issue persists, please share the pbix file with us. If it does contain sensitive data, you can upload to your OneDrive and share the link via private message. 

Best Regards,
Qiuyun Yu

[lorenzolancio]

You can download the Pbix file in this link.

https://1drv.ms/u/s!AuhjNzwzxebthBbZSBMaqtY5jDjR

Thank you very much 🙂

Lorenzo

[lorenzolancio · Accepted solution]

Hi @v-qiuyu-msft

I found a solution by myself. I have discovered the SUMMARIZE function 🙂

green = CALCULATE(DISTINCTCOUNT(TabellaMerge[COD_CLIENTE]);FILTER(SUMMARIZE(TabellaMerge;TabellaMerge[COD_CLIENTE];"sommafatt18";SUM(TabellaMerge[FATTURATO_2018]);"sommafatt17";SUM(TabellaMerge[FATTURATO_2017]));[sommafatt18]>[sommafatt17] && [sommafatt17]>0 && [sommafatt18]>0))

blue = CALCULATE(DISTINCTCOUNT(TabellaMerge[COD_CLIENTE]);FILTER(SUMMARIZE(TabellaMerge;TabellaMerge[COD_CLIENTE];"sommafatt17";SUM(TabellaMerge[FATTURATO_2017]));[sommafatt17]=0))

yellow = CALCULATE(DISTINCTCOUNT(TabellaMerge[COD_CLIENTE]);FILTER(SUMMARIZE(TabellaMerge;TabellaMerge[COD_CLIENTE];"sommafatt18";SUM(TabellaMerge[FATTURATO_2018]);"sommafatt17";SUM(TabellaMerge[FATTURATO_2017]));[sommafatt18]<[sommafatt17] && [sommafatt17]>0 && [sommafatt18]>0))

red = CALCULATE(DISTINCTCOUNT(TabellaMerge[COD_CLIENTE]);FILTER(SUMMARIZE(TabellaMerge;TabellaMerge[COD_CLIENTE];"sommafatt18";SUM(TabellaMerge[FATTURATO_2018]));[sommafatt18]=0))

Thank u very much for your suggestion, it helped me to understand how to do it.

byebye 🙂

Lorenzo

[lorenzolancio]

Hi @v-qiuyu-msft,

How can i share the Pbix file ?

I have only the free PowerBi Desktop version.

I think i can share it on OneDrive. What is the email address where i can share the report with you?

Thank you very much,

Lorenzo