Finding sum of unique values for set of columns

[SzymonP]

Hi all,

I'm only few hours into Power BI, though had my share with other tools, but I cannot find a way to do something, that's done by multiple steps in excel, so should be doable within DAX as well.

Imagine I have a big table, and there are four columns that are of interest for me, something like this:

Country	Product	Color	count
Poland	Car	Green	120
Poland	Car	Blue	120
Poland	Car	Red	120
Poland	Bus	Green	100
Poland	Bus	Red	100
Germany	Car	Green	200
Germany	Car	Blue	200
Germany	Car	Red	200
Germany	Bus	Green	150
Germany	Bus	Red	150

And depending on whether we want to see numbers by Product or Color, we will see numbers for countries like this:

By Product			By Color
Poland	220		Poland	560
Germany	350		Germany	900

Basically, it's like making a small pivot with only 2-3 columns out of the big set, and summarizing the max value. That's how it's done in excel.

I was working with something like this:

SumMax = CALCULATE(

MAX(Sheet1[count]),

ALLEXCEPT(Sheet1,Sheet1[Product]),

ALLEXCEPT(Sheet1,Sheet1[Color]))

But it obviously doesnt work - it just returns max per Country it finds. 

I've tried some workaround to have a sumx of count/count(Country) with summarize (...,Product), but then it doesn't work inside a table that has no product column, so it's bad.

Thanks a lot for any help or link to a tutorial that would cover this.

[SzymonP · Accepted solution]

Just for the future reference, unfortunately none of the proposed solutions worked like a charm, though brought me closer to finding a working one, so big thanks to all the replies.

This is how I have done that:

1. I've created concatenated column inside the data source CPD =[country]&[product]&[date].date

2. I've created a rankx column (not measure): CPD_rank = 

= RANKX(FILTER(Sheet1,Sheet1[CPD]=EARLIER(Sheet1[CPD])),Sheet1[count]+rand(),,desc,skip)  //the rand part is essential, since I don't want tied 1st places

3. Now I've created measure that is a simple

= CALCULATE(sum(Sheet1[count]),Sheet1[CPD_rank]=1)

and I've replaced the steps for the second scenario, just added one more column.
I'm fairly sure it's not most efficient solution, but given the size of the data (up to few k rows) it works as intended.

[HotChilli]

So the logic is different between the 2 tables (not just different between Product and Color)?

First table is Country and a measure:

MeasureProduct = 
var _tabX = ADDCOLUMNS(SUMMARIZE(TableY, TableY[Product], TableY[count]), "MaxVal", CALCULATE(MAX(TableY[count])))
RETURN
SUMX(_tabX, [MaxVal])

The second one is just a country, SUM(count).  I'm not sure if this is what you really want for the second table but that's what it reduces to.

[tamerj1]

Hi @SzymonP 

from the visuals pane select "Table". First Drag the country into the values. Once there, click on the down arrowans make sure that "don't summarize" is selected. The do the same for "color". Then do the same for the "count" but this time select "Sum". 
you can create another table but this time use the "Product" instead of the "Color"

[SzymonP]

Hi, thanks, but this would leave me with two columns (Country and Product) instead of one.

[HotChilli]

Poland Car = 120.  Is this the MAX from within the group?

--

Can you explain the 2nd table (by color) please? It looks like the totals are returned i.e. different from the first table logic

[SzymonP]

Hey,

The 120 is the max/ average/ single value - whichever you like more. Second table logic is the same, the hidden table would be the whole presented table, as each row is unique.
If this was SQL, I would do second table like this:

select country, sum(count) from (

select distinct country, product, color, count

from table)

group by country

and first like this:

select country, sum(count) from (

select distinct country, product, count

from table)

group by country

I'm really bad at describing the logic sometimes 🙂

[SzymonP]

Sorry for the confusion, I could've written more indeed.

The value in column count is corresponding 1-1 to each value in the "Product" column, though can differ month to month, but that's another topic.

To calculate sum, we take one row per each unique value in the column Product (first table) or Color (second table).

So we act like there's hidden column, and provide only the total amount. e.g. first "hidden" table would look like

Country	Product	Count
Poland	Car	120
Poland	Bus	100

And result in:

Poland 220

[HotChilli]

What's the logic for the 2 tables shown?