Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Excel like cell based formulas in PowerBI

I am trying to replicate a table in Excel in PowerBI that looks like below. Every Measure is coming from a different table, ie, i have 3 different tables that contain time bills, material bills, jobs received/closed. In Excel, it is pretty easy i just use sumifs for bills, and countifs for jobs and just apply various criteria differently in each cell, based on cell coordinates. But i am not sure how to do this in Powerbi, as formulas in power bi are column based, plus the first column's measures are coming from all the different tables. 

Does anyone have an idea how to implement it best in powerbi? maybe 16 KPI visuals just stackedup togethere to create an illusion of table?

(LY Means Last Year) and i have a separate calendar table in power bi to filter all the connected 4 tables. 

 
 
MeasuresMTDYTDLY MTDLY YTD
Time Bills    
Material Bills    
jobs Received    
jobs Closed    

 

this table cna also look like this:

MeasuresTime BillsMat BillsJobs ReceivedJobs Closed
MTD    
YTD    
LY MTD    
LY YTD    
Need Help

1 Reply

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    6 years ago

    Hi Anonymous ,

     

    You don't need to create the illusion of a table, you can create a table but you need to setup the ground base with disconnected tables:

     

    You need to create to tables for your measures:

    ID Time Fram
    1 MTD
    2 YTD
    3 LY MTD
    4 LY YTD

     

    ID Measure
    1 Time Bills
    2 Material Bills
    3 Jobs Received
    4 Jobs Closed

     

    Now assuming you have a measure for each of the ones refered previously you need to do the following measure:

    Table Value =
SWITCH (
    TRUE (),
    SELECTEDVALUE ( TimeFrame[ID] ) = 1
        && SELECTEDVALUE ( Measures[ID] ) = 1, TOTALMTD ( [TimeBills], Date[Date] ),
    SELECTEDVALUE ( TimeFrame[ID] ) = 2
        && SELECTEDVALUE ( Measures[ID] ) = 1, TOTALYTD ( [TimeBills], Date[Date] ),
    SELECTEDVALUE ( TimeFrame[ID] ) = 1
        && SELECTEDVALUE ( Measures[ID] ) = 2, TOTALMTD ( [Material Bills], Date[Date] ),
    SELECTEDVALUE ( TimeFrame[ID] ) = 2
        && SELECTEDVALUE ( Measures[ID] ) = 2, TOTALMTD ( [Material Bills], Date[Date] ),
...
)

    Fill the .. with the rest of the combinations.

    Now just create a matrix visualization with the two previous tables on columns and rows and the previous measure on values.