Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    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)

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

Total customer:

 

total customer = 
    VAR __Date = MAX('Table'[date])
    VAR __Country = SELECTCOLUMNS('Table',"Country",[country])
    VAR __Table = 
        SUMMARIZE(
            FILTER(
                ALL('Table'),
                [country] IN __Country
            ),
            [customer id],
            "Date",MIN('Table'[date])
        )
    VAR __Count = COUNTROWS(FILTER(__Table,[Date]<=__Date))
RETURN
    IF(ISBLANK(__Count),0,__Count)