Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
or like this?
To achieve this:
1) Use a bar chart
2) as a dimension : Department
3) as measures:
Original % Total:
Sum([Revenue Original]) / (sum({<Department>}[Revenue Estimated])+sum({<Department>}[Revenue Original]))
Estimated % Total:
Sum([Revenue Estimated]) / (sum({<Department>}[Revenue Estimated])+sum({<Department>}[Revenue Original]))
4) Under aspect > General : add a title as follow:
='Nbr of Departments to PASS : '&(count({<Department={"=(Sum([Revenue Original]) / sum([Revenue Estimated]))>1"}>}Department)) &' | Nbr of Departments to FAIL : '&(count({<Department={"=(Sum([Revenue Original]) / sum([Revenue Estimated]))<=1"}>}Department))
You can also add as a subtitle:
='Departments to PASS : '& concat
( aggr(only({<Department={"=(Sum([Revenue Original]) / sum([Revenue Estimated]))>1"}>}Department),Department),', ')
&
' | Departments to FAIL : '& concat
( aggr(only({<Department={"=(Sum([Revenue Original]) / sum([Revenue Estimated]))<=1"}>}Department),Department),', ')
to have also the list of departments that pass and fail:
You can also add yet another bar chart; to see how much was done by department compared to estimated:
Dimension: Department
Measure: Sum([Revenue Original]) / sum([Revenue Estimated])
and under aspect> colors > color by expression:
if(Sum([Revenue Original]) / sum([Revenue Estimated])>1,green(),red())
Result:
If u want to sort the 2 charts, by the percentage of realisation (original % estimated):
1) in the first chart; under the sort tab: put the measure above the dimension:
2) in the second bar chart; under the sort tab; sort the dimension Department by expression (descending):
Sum([Revenue Original]) / sum([Revenue Estimated])
result:
