Search code examples
excelexcel-formuladaxpowerpivot

Excel Pivot Chart show empty column when data is based on DAX formulas

I appreciated that this question may be really daft, but I'm stumped.

I have a table from which a pivot table is created from. The base table is ostensibly a Testing schedule, I'm interested in 3 fields in my dashboard

enter image description here

In my dashboard I use a couple of measures to work out the number of cases that aren't passed 'cases remaining' and what that percent consists of 'remaining percent less passed'.

enter image description here

What I want is the 'Execution Priority' to always show all values - Critical, High, Medium and Low.

I'm really not sure how to do it, as 'Execution Priority' is just a standard column, it's not calculated. I've forced a '0' to appear in the rows when all cases are set to 'pass' using a '+0' on the end of the measure '=COUNTROWS(FILTER(Test_Schedule,Test_Schedule[Result] <> "Pass"))+0'

Any help?

Solution


  • You can achieve your result by using CUBEVALUE and CUBEMEMBER functions.
    Less convinient than regular PivotTable but PivotTable will drop and/or add values that are added to the model, therefore you can't really achieve "static" dashboard using PivotTable.

    What I suggest for your isuue: 1. in dax create 2 measures:

    countRows:=COUNTROWS(Table2)

countRowsNotPass:=IF(ISBLANK(COUNTROWS(FILTER(Table2,Table2[Result]<>"Pass"))),0,COUNTROWS(FILTER(Table2,Table2[Result]<>"Pass")))

    1. In Excel create following calls for the measures

      =CUBEMEMBER("ThisWorkbookDataModel","[Measures].[countRows]") =CUBEMEMBER("ThisWorkbookDataModel","[Measures].[countRowsNotPass]")
      I have named these cells as CountR and CountN

    2. Create a PivotTable from the model containing only "Functional area" at rows

    3. Create a Cubemembers for each of the following: "Critical,High,Medium,Low,etc..." like so (just change the highlighted Critical for each one in a group):

      =IFERROR(CUBEMEMBER("ThisWorkbookDataModel","[Table2].[Execution Priority].&[Critical]"),"Critical")

    5.Create a function like this, this one should get you number of cases remaining: =IFERROR(CUBEVALUE("ThisWorkbookDataModel",CUBEMEMBER("ThisWorkbookDataModel","[Table2].[Functional Area].&["&$F4&"]"),Crit,CountN),0)

    1. create another function this should give you a percentage of remaining cases:

    =IFERROR(G4/CUBEVALUE("ThisWorkbookDataModel",CUBEMEMBER("ThisWorkbookDataModel","[Table2].[Functional Area].&["&$F4&"]"),Crit,CountR),0)

    You should end up with something like that: Dashboard preview


    Let me know if that helped