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

     

v-qiuyu-msft's avatar
v-qiuyu-msft
Icon for Community Support rankCommunity Support
8 years ago

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'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