Forum Discussion

pmcinnis's avatar
pmcinnis
Icon for Helper III rankHelper III
6 years ago
Solved

How to create a table that groups and counts things from another table and does a calculation

I have a table of chemical concentration measurements for hundreds of chemicals (call it the original table). Each chemical was sampled and measured many times so each chemical has many rows of concentration data. The two main columns are the chemical name and the concentration. The concentration column contains a number if the chemical was found in a sample or is null if the chemical wasn't found. I need to create a new table from the original table that has one row per chemical and the following four columns: (i) Chemical name (ii) Count of how many rows there are for that chemical (iii) Count of how many rows of that chemical have a concentration number (i.e. not null) (iv) Calculation of column iii divided by column (ii), expressed as a percentage.

 

I'm new to Power BI and have been studying videos and a DAX function reference document (from Microsoft Docs) but am currently at a loss to figure this out. Seeing one example worked out (the above scenario) would accelerate learning DAX and table creation in a big way.

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi pmcinnis 

    Create measures 

    all_noblank_rows = CALCULATE(COUNTROWS('Table'),FILTER(ALLEXCEPT('Table','Table'[chemical name]),[concentration]<>BLANK()))

allrows = CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[chemical name]))

divide% = [all_noblank_rows]/[allrows]

    Or create a table

    Table 2 =
ADDCOLUMNS (
    SUMMARIZE (
        'Table',
        'Table'[chemical name],
        "count_all", COUNTROWS ( 'Table' ),
        "count_noblank", COUNT ( 'Table'[concentration] )
    ),
    "5", [count_noblank] / [count_all]
)

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi pmcinnis 

    Create measures 

    all_noblank_rows = CALCULATE(COUNTROWS('Table'),FILTER(ALLEXCEPT('Table','Table'[chemical name]),[concentration]<>BLANK()))

allrows = CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[chemical name]))

divide% = [all_noblank_rows]/[allrows]

    Or create a table

    Table 2 =
ADDCOLUMNS (
    SUMMARIZE (
        'Table',
        'Table'[chemical name],
        "count_all", COUNTROWS ( 'Table' ),
        "count_noblank", COUNT ( 'Table'[concentration] )
    ),
    "5", [count_noblank] / [count_all]
)

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • pmcinnis's avatar
      pmcinnis
      Icon for Helper III rankHelper III
      6 years ago

      Thanks Maggie for your wonderful response. The DAX scripts, images and .pbix file together are the perfect solution. I've learned a lot from it!

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi pmcinnis 

    Create measures and add to a table visual,

    allrows = CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[chemical name]))

all_noblank_rows = CALCULATE(COUNTROWS('Table'),FILTER(ALLEXCEPT('Table','Table'[chemical name]),[concentration]<>BLANK()))

divide% = [all_noblank_rows]/[allrows]

     

    Or create  anew table

    Table 2 =
ADDCOLUMNS (
    SUMMARIZE (
        'Table',
        'Table'[chemical name],
        "count_all", COUNTROWS ( 'Table' ),
        "count_noblank", COUNT ( 'Table'[concentration] )
    ),
    "5", [count_noblank] / [count_all]
)

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.