Aggregation to avoid accounting twice

[antonioaceituno]

Hi,

I was wondering if any of you may help me with one sintaxis issue I have:

I have one table in my datasert: Column A: Country: Spain, Italy, England. Column B:Date (dd/mm/yyyy), Column C: Daily target, Column 😧 Element Related.

DailyT arget would be equal in all the rows as long as the Day of the year and Country are the same. No matter the Element. 1 Target per day and country

I.e:

Spain // 1-1-18 // 50 // Generator

Italy // 1-1-18 // 40 // Cables

England // 1-1-18 // 30 // Fuses

Spain // 1-1-18 // 50 // Fuses

Spain //1-1-18 // 50// Blade

Italy // 2-1-18 // 5 // Generator

England // 2-1-18 // 15 // Blade

Spain //2-1-18 // 12 // Cables

Spain //2-1-18 // 12 // Fuses

...

If I Filter by Spain & Italy, & the January 1st, I should get 90 (50:Daily target for Spain, + 40: Daily target for Italy).

NOT 50+40+50+50. 

If I filter by Spain, and 1st and 2nd Jan, I should get 62 (50: 1st Jan Target for spain + 12: 2nd Jan Target for Spain). NOT 50+50+50+12+12

Do I have to create a new measure? and how? I was told to use SUMX and DISTINCT function but I dont really get it and dont know how to create it

Or do I have to create a new column instead? How it would be?

Thank you so much in advance!

[antonioaceituno · Accepted solution]

Thank you so much for your help!

It almost works, but it does not when I select different dates at the same time. Selecting more days creates conflict. 

When selecting Spain & Italy days 1 & 2, the result should be

Day 1: 50+40

Day 2: 12+5

Result: 90+17=107

However, the "Card plot" says "MdxScript(Model) (3,70) Calculation error in measure 'Table1[Measure]: A table of multiple values was supplied where a single value was expected"

Thank you so much again!

[Zubair_Muhammad]

Hi @antonioaceituno

Try this MEASURE

Measure =
SUMX (
    ALLSELECTED ( Table1[Country] ),
    CALCULATE ( DISTINCT ( Table1[Daily Target] ) )
)

[antonioaceituno · Accepted solution]

Thank you so much for your help!

It almost works, but it does not when I select different dates at the same time. Selecting more days creates conflict. 

When selecting Spain & Italy days 1 & 2, the result should be

Day 1: 50+40

Day 2: 12+5

Result: 90+17=107

However, the "Card plot" says "MdxScript(Model) (3,70) Calculation error in measure 'Table1[Measure]: A table of multiple values was supplied where a single value was expected"

Thank you so much again!

[Zubair_Muhammad · Accepted solution]

HI @antonioaceituno

Try this revised MEASURE

Measure =
SUMX (
    SUMMARIZE ( ALLSELECTED ( Table1 ), Table1[Country], Table1[Date] ),
    CALCULATE ( DISTINCT ( Table1[Daily Target] ) )
)

[antonioaceituno]

Thank you! so useful! 

[Zubair_Muhammad]

@antonioaceituno

[Zubair_Muhammad]

@antonioaceituno

See the attached file here