Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello everyone,
i created a report with the matrix visual. the table should show a key figure for each vehicle in rows and a comparison of the key figures for each type of data. the following example shows an extract from the table to give a better understanding of this
At the end of the column, at the end of the month, I would like to display the total for each data type and the deviation "Ist" vs. "Plan". I am currently missing an approach as to how I could implement this. the options of the matrix visual only allow a sub-total and the total. but i need the deviation of the total
Thank you for your reply.
I have followed your approach further and with my own adaptation of the measures as follows
the deviation is even calculated in the column:
Sample Data:
Equipment | Datum | KM | Datenart |
EQ1 | 02.01.2020 | 4 | Plan |
EQ1 | 02.01.2020 | 15 | Plan |
EQ1 | 02.01.2020 | 40 | Plan |
EQ1 | 02.01.2020 | 100 | Plan |
EQ1 | 02.01.2020 | 125 | Plan |
EQ1 | 08.09.2020 | 167 | Fahrleistung |
EQ1 | 11.09.2020 | 181 | Fahrleistung |
EQ1 | 14.09.2020 | 40 | Fahrleistung |
EQ1 | 07.10.2020 | 250 | Fahrleistung |
EQ1 | 13.10.2020 | 262 | Fahrleistung |
EQ1 | 31.12.2019 | 0 | Ist |
EQ1 | 31.12.2019 | 0 | Ist |
EQ1 | 31.12.2019 | 0 | Ist |
EQ1 | 31.12.2019 | 0 | Ist |
EQ1 | 31.12.2019 | 0 | Ist |
Hi @Simons_BI ,
Currently only row totals can use measure to modify it, column totals could not do this becasue the column total values based on the values in the Matrix.
You can create these measures to calulate row totals:
_LST =
IF (
HASONEVALUE ( 'Table'[Category] ),
SUM ( 'Table'[lst] ),
IF (
HASONEVALUE ( 'Table'[Date] ),
SUM ( 'Table'[lst] ) - SUM ( 'Table'[Plan] ),
SUM ( 'Table'[lst] ) - SUM ( 'Table'[Plan] )
)
)
_plan =
IF (
HASONEVALUE ( 'Table'[Category] ),
SUM ( 'Table'[Plan] ),
IF (
HASONEVALUE ( 'Table'[Date] ),
SUM ( 'Table'[lst] ) - SUM ( 'Table'[Plan] ),
SUM ( 'Table'[lst] ) - SUM ( 'Table'[Plan] )
)
)
Attached a sample file in the below, hopes to help you.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Simons_BI ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |