Forum Discussion
Dynamic aggregation and calculation
Hello guys I am trying to translate a nested sql query on power bi and keep the ability to use the first level fields as slicers Here is the scenario The raw data looks like this custome...
Here is second sum. PBIX with all three measures is included as an attachment below.
second sum = VAR __Date = MAX('Table'[date]) VAR __Country = SELECTCOLUMNS('Table',"Country",[country]) VAR __Table = SUMMARIZE( FILTER( ALL('Table'), [country] IN __Country ), [date], "Sum",SUM('Table'[amount]) ) VAR __Sum = SUMX(FILTER(__Table,[date] = __Date),[Sum]) RETURN IF(ISBLANK(__Sum),0,__Sum)
Here is second sum. PBIX with all three measures is included as an attachment below.
second sum =
VAR __Date = MAX('Table'[date])
VAR __Country = SELECTCOLUMNS('Table',"Country",[country])
VAR __Table =
SUMMARIZE(
FILTER(
ALL('Table'),
[country] IN __Country
),
[date],
"Sum",SUM('Table'[amount])
)
VAR __Sum = SUMX(FILTER(__Table,[date] = __Date),[Sum])
RETURN
IF(ISBLANK(__Sum),0,__Sum)
Thx a lot Greg_Deckler !!! 😄
- Happy to help! 🙂