For my report i needed to unpivot my table so it looks like this
I basicly have cleaned my data and unpivoted them for use in my report. I use cube functions to extract data (Cube value function IFERROR(N(CUBEVALUE("ThisWorkbookDataModel";"[Measures].["&$E$2&"]";"[OHv_PQ].[MJESEC].["&$E$3&"]";"[OHv_PQ].[KONTO].["&$D60&"]";"[OHv_PQ].[SCENARIO].["&I$5&"]";Slicer_MT));0) ). It works fine to extract data per month or year ia have writen a simple MTD and YTD measure, and control them by slicer.
Problem happens when i want to compere values between different scenarios for example. I look for diference betwen actual period and budget values and also i want to see percentual change.
For last part i have just subtrated cube values (i know that was not the smartest idea) for example Cube value"Actual" - Cube value"Budget", this is where things break a part. Slice for this part woudnot acepte when i change cost cente it always stay on whole year or month and just does not wnat to slice for diferences per cost center.
My question is it posible to write dax measure when i have pivot table as mine to calculate differences and percentual change and contol them by slicer. I have forgot to say that on slicer i only slice data per cost center
I do this in excel and not in PowerBi just to clerifay.
| ASFASFAA.xlsx | |||||||
|---|---|---|---|---|---|---|---|
| D | E | F | G | H | |||
| 4 | Account | Cost Center | Scenario | Month | Value | ||
| 5 | 11111 | 1 | 2020 | 1 | 611 | ||
| 6 | 11111 | 1 | 2020 | 2 | 1967 | ||
| 7 | 11111 | 1 | 2020 | 3 | 1360 | ||
| 8 | 11111 | 1 | 2020 | 4 | 435 | ||
| 9 | 11111 | 1 | 2020 | 5 | 2416 | ||
| 10 | 11111 | 1 | 2020 | 6 | 28 | ||
| 11 | 11111 | 1 | 2021 | 1 | 796 | ||
| 12 | 11111 | 1 | 2021 | 2 | 2302 | ||
| 13 | 11111 | 1 | 2021 | 3 | 1071 | ||
| 14 | 11111 | 1 | 2021 | 4 | 1746 | ||
| 15 | 11111 | 1 | 2021 | 5 | 1947 | ||
| 16 | 11111 | 1 | 2021 | 6 | 326 | ||
| 17 | 11111 | 2 | 2020 | 1 | 648 | ||
Sheet1 | |||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| H5:H17 | H5 | =+RANDARRAY(13,,15,2520,TRUE) |
| Dynamic array formulas. | ||
I basicly have cleaned my data and unpivoted them for use in my report. I use cube functions to extract data (Cube value function IFERROR(N(CUBEVALUE("ThisWorkbookDataModel";"[Measures].["&$E$2&"]";"[OHv_PQ].[MJESEC].["&$E$3&"]";"[OHv_PQ].[KONTO].["&$D60&"]";"[OHv_PQ].[SCENARIO].["&I$5&"]";Slicer_MT));0) ). It works fine to extract data per month or year ia have writen a simple MTD and YTD measure, and control them by slicer.
Problem happens when i want to compere values between different scenarios for example. I look for diference betwen actual period and budget values and also i want to see percentual change.
For last part i have just subtrated cube values (i know that was not the smartest idea) for example Cube value"Actual" - Cube value"Budget", this is where things break a part. Slice for this part woudnot acepte when i change cost cente it always stay on whole year or month and just does not wnat to slice for diferences per cost center.
My question is it posible to write dax measure when i have pivot table as mine to calculate differences and percentual change and contol them by slicer. I have forgot to say that on slicer i only slice data per cost center
I do this in excel and not in PowerBi just to clerifay.
