Count values

[sboinala]

I have two tables

Customers

a

b

c

d

e

f

Account Type:

Customer	Fuel
a	Gas
a	Elec
b	Gas
c	Gas
c	Elec
d	Gas
d	Elec
e	Elec
e	Elec
f	Gas
f	Gas

Required Output

Customer	Gas & Elec Counts	Gas Only Count	Elec Only Count
a	1
b	0	1
c	1
d	1
e	0		1
f		1

The Visulation matrix would be :

Gas & Elec Counts	3
Gas  Counts	2
Elec Counts	1

[v-gizhi-msft · Accepted solution]

Hi,

Please try these three measures first:

Gas & Elec Counts = 
SUMX (
    DISTINCT ( Customer[Customers] ),
    CALCULATE (
        IF (
            CALCULATE (
                DISTINCTCOUNT ( 'Account Type'[Customer&Fuel] ),
                FILTER (
                    ALLSELECTED ( 'Account Type' ),
                    'Account Type'[Customer] IN FILTERS ( Customer[Customers] )
                )
            ) > 1,
            1,
            0
        )
    )
)

Gas Only Count = 
SUMX (
    DISTINCT ( Customer[Customers] ),
    CALCULATE (
        IF (
            CALCULATE (
                DISTINCTCOUNT ( 'Account Type'[Customer&Fuel] ),
                FILTER (
                    ALLSELECTED ( 'Account Type' ),
                    'Account Type'[Customer] IN FILTERS ( Customer[Customers] )
                )
            ) = 1
                && CALCULATE (
                    MAX ( 'Account Type'[Fuel] ),
                    FILTER (
                        ALLSELECTED ( 'Account Type' ),
                        'Account Type'[Customer] IN FILTERS ( Customer[Customers] )
                    )
                ) = "Gas",
            1,
            0
        )
    )
)

Elec Only Count = 
SUMX (
    DISTINCT ( Customer[Customers] ),
    CALCULATE (
        IF (
            CALCULATE (
                DISTINCTCOUNT ( 'Account Type'[Customer&Fuel] ),
                FILTER (
                    ALLSELECTED ( 'Account Type' ),
                    'Account Type'[Customer] IN FILTERS ( Customer[Customers] )
                )
            ) = 1
                && CALCULATE (
                    MAX ( 'Account Type'[Fuel] ),
                    FILTER (
                        ALLSELECTED ( 'Account Type' ),
                        'Account Type'[Customer] IN FILTERS ( Customer[Customers] )
                    )
                ) = "Elec",
            1,
            0
        )
    )
)

Then create a slicer table by Enter Data:

Then try this count measure:

Count =
SUMX (
    DISTINCT ( 'Slicer Table'[Category] ),
    CALCULATE (
        SWITCH (
            MAX ( 'Slicer Table'[Category] ),
            "Elec Counts", [Elec Only Count],
            "Gas  Counts", [Gas Only Count],
            "Gas & Elec Counts", [Gas & Elec Counts]
        )
    )
)

The result shows:

Here is my test pbix file:

pbix 

Hope this helps.

Best Regards,

Giotto Zhi

[az38]

Hi @sboinala 

I see 2 required outputs in your post? what do you need exactly?

for first matrix just creatte a visual matrix, drop Customer to rows field, Fuel into Columns, Customer to values and set Values aggregation parameter as Count (Distinct)

for second, fuel - as rows, customer as Values and the same set Values aggregation parameter as Count (Distinct)

[sboinala]

The output i am looking for is :

Gas & Elec	3
Gas	2
Elec	1

I have tried the below ,however i couldnt get the first value  Gas & Elec

[az38]

@sboinala 

create a column

FuelType = 
var _isGas = CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[Customer]),'Table'[Fuel]="Gas")
var _isElec = CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[Customer]),'Table'[Fuel]="Elec")
RETURN
SWITCH(TRUE(),
_isElec > 0 && _isGas > 0, "Gas & Elec",
_isElec > 0, "Elec Only",
_isGas > 0, "Gas Only"
)

then add new column FuelType as rows and Count(Distinct) of Customers as Value