Forum Discussion

sana_sagheer's avatar
sana_sagheer
Advocate I
8 years ago
Solved

Calculation, single chart based on 3 measures

I have this issue, I am having a Reveneue KPI thats comprised of 3 components(each is a separate measure). I want my bar chart to show the revenue as total in first layer of hierarchy. and want to di...
Need Help
  • sana_sagheer's avatar
    sana_sagheer
    8 years ago

    Thanks everyone, we acheived the solution using Switch function and added component type table as selector for selecting one component type or the other. 

Eric_Zhang's avatar
Eric_Zhang
Microsoft Employee
8 years ago
sana_sagheer wrote:

I have this issue, I am having a Reveneue KPI thats comprised of 3 components(each is a separate measure). I want my bar chart to show the revenue as total in first layer of hierarchy. and want to display each component revenue at subsequent node of hierarchy. 
like node 0 -> total revenue
node 1-> component 1  revenue

node 2-> component 2  revenue

node 3-> component 3  revenue

Please guide how can we do it?

 

Thanks in advance,
Sana

sana_sagheer

Maybe you can create a tricky measure to detect the hierarchy level and then show different measures accordingly. Just replace 1,2,3,4 with the measures in your case. See more in the attached pbix file.

Measure = 
SWITCH (
    TRUE (),
    HASONEVALUE ( yourTable[node0] ) && NOT ( ISFILTERED ( yourTable[node1] ) )
        && NOT ( ISFILTERED ( yourTable[node2] ) )
        && NOT ( ISFILTERED ( yourTable[node3] ) ), 1,  // in hierachy level 1
    HASONEVALUE ( yourTable[node0] ) && HASONEVALUE ( yourTable[node1] )
        && NOT ( ISFILTERED ( yourTable[node2] ) )
        && NOT ( ISFILTERED ( yourTable[node3] ) ), 2,  // in hierarchy level 2
    HASONEVALUE ( yourTable[node0] ) && HASONEVALUE ( yourTable[node1] )
        && HASONEVALUE ( yourTable[node2] )
        && NOT ( ISFILTERED ( yourTable[node3] ) ), 3,  // in hierarchy level 3
    4 //in hierarcy level 4
)