I have two tablesCustomersabcdef Account Type:CustomerFuelaGasaElecbGascGascElecdGasdEleceEleceElecfGasfGas Required OutputCustomerGas & Elec CountsGas Only CountElec Only Counta1 b01 c1 d1 e0 1f 1 The Visulation matrix would be :Gas & Elec Counts3Gas Counts2Elec Counts1

sboinala

Regular Visitor

6 years ago

Solved

Count values

I have two tables

Customers

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

Need Help

v-gizhi-msft

6 years ago

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

4 Replies

az38
Community Champion
6 years ago
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
  Regular Visitor
  6 years ago
  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
    Community Champion
    6 years ago
    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

v-gizhi-msft

Community Support

6 years ago

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

Forum Discussion

Count values

4 Replies

Related content

Counting nulls

Count of values not accurate

Count duplicate values

PIE Chart - Counting Values

issue count number of matching values.