Forum Discussion

lorenzolancio's avatar
lorenzolancio
Frequent Visitor
8 years ago
Solved

count rows of calculated column

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 th...
Data Modeling
Reports
  • lorenzolancio's avatar
    lorenzolancio
    8 years ago

    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's avatar
lorenzolancio
Frequent Visitor
8 years ago

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