Forum Discussion
Aggregation to avoid accounting twice
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 D: 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!
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!
Try this revised MEASURE
Measure = SUMX ( SUMMARIZE ( ALLSELECTED ( Table1 ), Table1[Country], Table1[Date] ), CALCULATE ( DISTINCT ( Table1[Daily Target] ) ) )
6 Replies
Try this MEASURE
Measure = SUMX ( ALLSELECTED ( Table1[Country] ), CALCULATE ( DISTINCT ( Table1[Daily Target] ) ) )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!
Try this revised MEASURE
Measure = SUMX ( SUMMARIZE ( ALLSELECTED ( Table1 ), Table1[Country], Table1[Date] ), CALCULATE ( DISTINCT ( Table1[Daily Target] ) ) )