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,

 

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

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

        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