Forum Discussion
I need to create a value within a matrix visualisation to calculate the differences between columns.
Within my data there are the following columns
Table Structure
What I am attempting to create in the visualisation is a difference column which I can then apply conditional formatting to with an up arrow, down arrow or - depending on the difference.
Matrix Visualisation
New to PowerBI so would appreciate any help that can be offered.
2 Replies
- amitchandak
Super User
Adampw123 , Create a Rank on date of run, Create a separate table say Period with only date of run
new column
Period Rank = RANKX(all("Period"),"Period"[Date of RUN],,ASC,Dense)
Then try measures
This Period = CALCULATE(sum(Table[Total Count]), FILTER(ALL('Period'),Period[Period Rank]=max(Period[Period Rank])))
Last Period = CALCULATE(sum(Table[Qty]), FILTER(ALL(Period),Period"[Period Rank]=max(Period[Period Rank])-1))you can diff measure and use those in conditional fromatting
diff = [This Period]-[Last Period]
diff % = divide([This Period]-[Last Period],[Last Period])refer if needed
conditional formatting by measure and apply it on pie?: https://youtu.be/RqBb5eBf_I4
Icon formatting
https://exceleratorbi.com.au/conditional-formatting-using-icons-in-power-bi/
https://community.powerbi.com/t5/Desktop/FORMAT-icon-set-for-use-in-a-data-card/td-p/811692 Thanks for the help. Apologies for the delay in replying. Starting to improve the result. However when calculating the difference i am getting the result of equalling the [This Period value]. Within the statements shown above you quote
"
Last Period = CALCULATE(sum(Table[Qty]), FILTER(ALL(Period),Period"[Period Rank]=max(Period[Period Rank])-1))
"
As my table doesn't contain a column of [Qty] i have swapped this over with the [Total Count] column.
Obviously what 'i, trying to achieve for example is to show an increase of 19 on the 1st August compared to the same value on 25th Jul for Vulnerable and 12 between etc. 7th Aug and 1st Aug.
