Calculate difference in percent grouped on Country and Date

Question

I already can calculate difference in percent grouped by date,

let t = (datatable(Value:int, Date:datetime, Сountry:string) 
[1000, '2018-01-01', "USA",    // 1 Jan == 100% 
 2000, '2018-01-01', "Poland", // this 100% also because grouped by Date 
 3000, '2018-01-02', "USA",    // 2 Jan == 233% because compared with 1 Jan
 4000, '2018-01-02', "Poland", // 233% also
]);

let val_2018 = toscalar(t | where Date >= datetime(2018-01-01) and Date < datetime(2018-01-02) | summarize sum(Value)); t | summarize percent = 100.0 * sum(Value) / val_2018 by Date

So i need to get 4 records like these:

1 Jan, USA, 100%
1 Jan, Poland, 100%
2 Jan, USA, 300%
2 Jan, Poland, 200%

but how add grouping by country as well? As i understood need to add "by Сountry" in all "summarize" but toscalar can't have "by" after "summarize" :(

Answer 1

You can use a combination of the prev() and iff() functions.

For example:

datatable(['value']: int, ['date']: datetime, ['country']: string) [
    1000, datetime(2018-01-01), "USA",   
    2000, datetime(2018-01-01), "Poland",
    3000, datetime(2018-01-02), "USA",   
    4000, datetime(2018-01-02), "Poland",
]
| order by country asc, ['date'] asc
| extend percentage = iff(
    prev(country) == country,
    100.0 * value / prev(value),
    100.0)

value	date	country	percentage
2000	2018-01-01 00:00:00.0000000	Poland	100
4000	2018-01-02 00:00:00.0000000	Poland	200
1000	2018-01-01 00:00:00.0000000	USA	100
3000	2018-01-02 00:00:00.0000000	USA	300