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

[pmcinnis]

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.

[v-juanli-msft · Accepted solution]

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]

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]

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.

[Ashish_Mathur]

Hi,

Share some data and show the expected result.