PowerBI Dax grouping company names by different countries and doing a count

user_19792251 0
Viewed 55

I am new to dax, would like to create a measure that allows me to see how many companies sold to different combinations of countries. For example, how many companies sold to all countries, or how many companies sold to just country A and B. I have tried using filters, they always return blank values. All the data is in this one table, please see screen shot below. Will appreciate your help

screenshot for sample data

dax powerbi
2 Answers

Hello Please try this:

1st Measure : Sales For Only A Country

SalesOnlyForA =
    COUNTROWS (
        CALCULATETABLE (
            SUMMARIZE ( YourTbl, YourTbl[Company], YourTbl[Country] ),
            YourTbl[Country] = "A"
        )
    )

2nd Measure : Sales For Only B Country

SalesOnlyForB =
    COUNTROWS (
        CALCULATETABLE (
            SUMMARIZE ( YourTbl, YourTbl[Company], YourTbl[Country] ),
            YourTbl[Country] = "B"
        )
    )

3rd Measure : Sales For All Countries:

SalesForAllCountries =
VAR OnlyA =
    CALCULATETABLE ( VALUES ( YourTbl[Company] ), YourTbl[Country] = "A" )
VAR OnlyB =
    CALCULATETABLE ( VALUES ( YourTbl[Company] ), YourTbl[Country] = "B" )
VAR OnlyC =
    CALCULATETABLE ( VALUES ( YourTbl[Company] ), YourTbl[Country] = "C" )
RETURN
    COUNTROWS ( INTERSECT ( INTERSECT ( OnlyA, OnlyB ), OnlyC ) )

Bonus- Extra : If you want to see all in one code:

Full_Code =
VAR SalesOnlyForA =
    COUNTROWS (
        CALCULATETABLE (
            SUMMARIZE ( YourTbl, YourTbl[Company], YourTbl[Country] ),
            YourTbl[Country] = "A"
        )
    )
VAR SalesOnlyForB =
    COUNTROWS (
        CALCULATETABLE (
            SUMMARIZE ( YourTbl, YourTbl[Company], YourTbl[Country] ),
            YourTbl[Country] = "B"
        )
    )
VAR SalesOnlyForC =
    COUNTROWS (
        CALCULATETABLE (
            SUMMARIZE ( YourTbl, YourTbl[Company], YourTbl[Country] ),
            YourTbl[Country] = "C"
        )
    )
VAR OnlyA =
    CALCULATETABLE ( VALUES ( YourTbl[Company] ), YourTbl[Country] = "A" )
VAR OnlyB =
    CALCULATETABLE ( VALUES ( YourTbl[Company] ), YourTbl[Country] = "B" )
VAR OnlyC =
    CALCULATETABLE ( VALUES ( YourTbl[Company] ), YourTbl[Country] = "C" )
VAR ABC =
    COUNTROWS ( INTERSECT ( INTERSECT ( OnlyA, OnlyB ), OnlyC ) )
RETURN
    ROW (
        "Sales A", SalesOnlyForA,
        "Sales B", SalesOnlyForB,
        "Sales C", SalesOnlyForC,
        "Sales All", ABC
    )

Result_Set:

GGV

user_194690880

You want to calculate total sales for combinations, Write this 3 measures :

Total Sales For A =
CALCULATE (
    [total sales],
    CALCULATETABLE ( VALUES ( YourTbl[Company] ), YourTbl[Country] = "A" )
)

Total Sales For B =
CALCULATE (
    [total sales],
    CALCULATETABLE ( VALUES ( YourTbl[Company] ), YourTbl[Country] = "B" )
)

Total Sales For All(ABC) =
VAR OnlyA =
    CALCULATETABLE ( VALUES ( YourTbl[Company] ), YourTbl[Country] = "A" )
VAR OnlyB =
    CALCULATETABLE ( VALUES ( YourTbl[Company] ), YourTbl[Country] = "B" )
VAR OnlyC =
    CALCULATETABLE ( VALUES ( YourTbl[Company] ), YourTbl[Country] = "C" )
VAR ABC =
    INTERSECT ( INTERSECT ( OnlyA, OnlyB ), OnlyC )
VAR Result =
    CALCULATE ( [total sales], ABC )
RETURN
    Result
user_194690880
Related