Forum Discussion
atziovara
Helper I
Helper IX-AXIS ISSUE
I have one measure called Current Demand which starts from 01/01/2012 (2012 H1) and ends in 01/01/2023 (2023 H1) and another one called Future Demand which starts from 30/12/2012 (2012 H2) and...
atziovaraHelper I
Helper I
- My data set is like this:
Table Name: TableBI
| Wave | Company ID | Weights | Questions | Answers | Weighted Answers | Periods |
| 1 | 1 | 0,006 | Demand change compared to the previous 6 months | -1 | -0,006 | 2022 H2 |
| 1 | 1 | 0,006 | Expected change in demand in the next 6 months | 1 | 0,006 | 2023 H1 |
| 1 | 1 | 0,006 | Liquidity problems intensity | 1 | 0,006 | 2022 H2 |
| 1 | 1 | 0,006 | Expected change in employment | 0 | 0 | 2023 H1 |
| 1 | 2 | 0,001 | Demand change compared to the previous 6 months | 1 | 0,001 | 2022 H2 |
| 1 | 2 | 0,001 | Expected change in demand in the next 6 months | 1 | 0,001 | 2023 H1 |
| 1 | 2 | 0,001 | Liquidity problems intensity | -1 | -0,001 | 2022 H2 |
| 1 | 2 | 0,001 | Expected change in employment | 1 | 0,001 | 2023 H1 |
| 1 | 3 | 0,0057 | Demand change compared to the previous 6 months | 1 | 0,0057 | 2022 H2 |
| 1 | 3 | 0,0057 | Expected change in demand in the next 6 months | 0 | 0 | 2023 H1 |
| 1 | 3 | 0,0057 | Liquidity problems intensity | 0 | 0 | 2022 H2 |
| 1 | 3 | 0,0057 | Expected change in employment | 0 | 0 | 2023 H1 |
| 2 | 1 | 0,006 | Demand change compared to the previous 6 months | 1 | 0,006 | 2023 H1 |
| 2 | 1 | 0,006 | Expected change in demand in the next 6 months | 0 | 0 | 2023 H2 |
| 2 | 1 | 0,006 | Liquidity problems intensity | 0 | 0 | 2023 H1 |
| 2 | 1 | 0,006 | Expected change in employment | 0 | 0 | 2023 H2 |
| 2 | 2 | 0,001 | Demand change compared to the previous 6 months | 1 | 0,001 | 2023 H1 |
| 2 | 2 | 0,001 | Expected change in demand in the next 6 months | 1 | 0,001 | 2023 H2 |
| 2 | 2 | 0,001 | Liquidity problems intensity | -1 | -0,001 | 2023 H1 |
| 2 | 2 | 0,001 | Expected change in employment | 1 | 0,001 | 2023 H2 |
| 2 | 3 | 0,0057 | Demand change compared to the previous 6 months | 1 | 0,0057 | 2023 H1 |
| 2 | 3 | 0,0057 | Expected change in demand in the next 6 months | 0 | 0 | 2023 H2 |
| 2 | 3 | 0,0057 | Liquidity problems intensity | -1 | -0,0057 | 2023 H1 |
| 2 | 3 | 0,0057 | Expected change in employment | 1 | 0,0057 | 2023 H2 |
*Weighted Answers is calculated as: Weights * Answers
**In the column "Answers" -1 denotes decrease (or zero problems for Liquidity problems intensity), 0 denotes stability (or low to medium intensity problems), and 1 denotes increase (or high intensity problems).
- I have created the calculated column "Period" in Power BI as follows:
Period =
IF(
'TableBI'[Wave] = 1,
IF(
('TableBI'[Questions] = "Demand change compared to the previous 6 months" ||
'TableBI'[Questions] = "Liquidity problems intensity"),
"2022 H2",
"2023 H1"
),
IF(
'TableBI'[Wave] = 2,
IF(
('TableBI'[Questions] = "Demand change compared to the previous 6 months" ||
'TableBI'[Questions] = "Liquidity problems intensity"),
"2023 H1",
"2023 H2"
)
)
)
)
***There are multiple waves in the original data
- I have created the following DAX measure in order to compute the weighted average of Current Demand and the weighted average of Future Demand:
Weighted Average =
VAR SelectedCharacteristic = SELECTEDVALUE('TableBI'[Questions])
RETURN
DIVIDE(
SUMX(
FILTER(
'TableBI',
NOT(ISBLANK('TableBI'[Weighted Answers])) &&
NOT(ISBLANK('TableBI'[Weights])) &&
'TableBI'[Questions] = SelectedCharacteristic
),
'TableBI'[Weights]
),
SUMX(
FILTER(
'TableBI',
NOT(ISBLANK('TableBI'[Weighted Answers])) &&
NOT(ISBLANK('TableBI'[Weights])) &&
'TableBI'[Questions] = SelectedCharacteristic
),
'TableBI'[Weights]
)
)
- I have placed a slicer with the values of column "Questions". I would like to be able to have the computed weighted average per Period both for Current Demand ("Demand change compared to the previous 6 months") and for Future Demand ("Expected change in demand in the next 6 months") without creating different measures every time I want to compare the weighted average of the answers of two or more questions.
- I created a disconnected table called 'Table2' by duplicating 'TableBI'. I also created another calculated column called 'Period' on the new table with the same formula as the first.
- I included a slicer that takes its values from 'Table2'[Questions].
- I created a new measure on Table2:
Weighted Average Disconnected =
CALCULATE(
[Weighted Average],
TREATAS(
VALUES('Table2'[Questions]),
TableBI[Questions]
)
)
- I added a line and stacked column graph. Its Column Values is the Weighted Average (from 'TableBI'), and its Line Values is Weighted Average Disconnected (from 'Table2'). I selected "Expected change in demand in the next 6 months" on the first slicer (from 'TableBI') and "Demand change compared to the previous 6 months" on the second slicer ('Table2').
- I want to have Period on the Shared Axis. I want to be able to show the fact that Current Demand starts on 2022 H2 and ends on 2023 H1, as well as that Future Demand is 6 months ahead, which means that it is blank on 2022 H2, as it starts on 2023 H1and ends on 2023 H2 (when Current Demand is blank). However, when the Shared Axis takes its values from "Period" of 'TableBI' the data of the graph start from 2023 H1 and end on 2023 H2. And when the Shared Axis takes its values from "Period" of 'Table2' the data of the graph start from 2022 H2 and end on 2023 H1.