Summarize data table that has various scenarios

[wsspglobal]

Hi

I have the following table that has company data categorized in broader categoreis (labeled as capital letters) and sub-categories (capital letters and a number). There are 3 main scenarios in this table, 1) Category available, but SubCategory not available , 2) No information in a certain year 3) No information in all years. How do I summarize the number of companies in each scenario?

companyid	FiscalYear	Category	SubCategory
222135	2011	A	A1
222135	2011	A	A2
222135	2011	B	B1
222135	2011	C
222135	2012	D	D1
222135	2012	D	D2
222135	2012	F	F1
222135	2012	F	F2
222135	2012	F	F3
222135	2013	A	A1
222135	2013	B	B1
222135	2013	B	B2
222135	2013	G	G1
222135	2013	G	G2
222135	2013	L	L1
222135	2014
222135	2014
222135	2014
222135	2015	C	C1
222135	2015	C	C2
222135	2015	C	C3
222135	2015	C	C4
222135	2015	B	B1
222135	2015	B	B2
222135	2017	A	A1
222135	2017	A	A2
222135	2017	A	A3
222135	2017	B	B1
222135	2017	B	B2
222135	2017	B	B3
222135	2017	C
222135	2017	M
222135	2017	L
222135	2017	G
222135	2017	H	H1
222135	2017	H	H3
222327	2016	A
222327	2016	B
222327	2016	C
222327	2016	D
222327	2016	E
222327	2016	F
222327	2016	G
224055	2011
224055	2011
224055	2012
224055	2012
224055	2013
224055	2013
224055	2014
224055	2014
224055	2015
224055	2015
224055	2016
224055	2016
224055	2017
224055	2017
224055	2018
224055	2018

[v-kelly-msft · Accepted solution]

Hi @wsspglobal ,

What you need is 3 measures as below：

scenario 1 = CALCULATE(DISTINCTCOUNT('Table'[companyid]),filter('Table','Table'[Category]<>BLANK()&&'Table'[SubCategory]=BLANK()))

scenario 2 = 
CALCULATE(DISTINCTCOUNT('Table'[companyid]),FILTER('Table','Table'[Category]=BLANK()&&'Table'[SubCategory]=BLANK()),'Table'[FiscalYear]=2011)

scenario 3 = 
COUNTROWS (
    FILTER (
        SUMMARIZE (
            'Table',
            'Table'[companyid],
           "Condition", IF (
                CALCULATE (
                    COUNTROWS ( 'Table' ),
                    FILTER (
                        'Table',
                        'Table'[Category] = BLANK ()
                            && 'Table'[SubCategory] = BLANK ()
                    )
                )
                    <> COUNTROWS ( 'Table' ),
                FALSE (),
                TRUE ()
            )
        ),
        [Condition] = TRUE ()
    )
)

Then you will see as below:

For the related .pbix file,you can turn to the URL: https://microsoftapc-my.sharepoint.com/:u:/g/personal/v-kellya_microsoft_com/EWEwraSQ_d9Emhnc_mUnFyc...

Hope this would help.

Best Regards,

Kelly

[amitchandak]

Try

case 1 = calculate(count(table[companyid]),filter(table,not(isblank(Category)),isblank(SubCategory )))

case 2 = calculate(sumx(values(table[year],table[case 1])),filter(table,isblank[case 1]))
case 3 = calculate(sumx(table,table[case 1]),filter(table,isblank[case 1]))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601