2

I have the following data source:

table1

My pivot rows are Team => Project Name with "Value" column in the Values. I am calculating the % ration of all projects that have value "True" compared to all projects that have a value (disregarding those without values). Here's the formula I use in PowerPivot:

=CALCULATE(COUNTROWS(),'Table'[Value]=TRUE()) / CALCULATE(COUNTROWS(), ('Table'[Value]=FALSE() || 'Table'[Value]=TRUE()), ISLOGICAL('Table'[Value]))

The formula works, however I only need to see this percentage on the "Team" level, the expanded projects should still have "True/False" values. Is this possible? Preferably, without VBA.

vard
  • 4,057
  • 2
  • 26
  • 46
Natalia
  • 157
  • 1
  • 2
  • 8

1 Answers1

0

Format your code. If you like reading very long lines, that's fine, but use DAX Formatter for the rest of us.

True vs All = 
CALCULATE(
    COUNTROWS( 'Table' )    // It's considered a best practice
                            // to explicitly name the table in
                            // COUNTROWS()
    ,'Table'[Value]=TRUE()
) / CALCULATE(
        COUNTROWS( 'Table' )
        // You can remove the test for [Value] = TRUE() || 
        // [Value] = FALSE()
        ,ISLOGICAL('Table'[Value])
    )

ConditionalDisplay = 
IF(
    ISFILTERED( 'Table'[Project] ) 
        && HASONEVALUE( 'Table'[Project] )
    ,VALUES( 'Table'[Value] )
    ,[True vs All]
)

[True vs All] is a cleaned up version of your existing measure.

[ConditionalDisplay] does what its name says. Displays a different value based on conditions.

We check for ISFILTERED() to cover the edge case where a given value of [Team] has only a single project. We check for HASONEVALUE() to cover the case where an explicit filter (slicer or filter) exists on [Project], but more than one is in context (grand total level).

When the two are true, we return VALUES( 'Table'[Value] ), the column made up of the distinct values in [Value]. This is only evaluated when we already know there's exactly one distinct value. A 1x1 table is implicitly converted to scalar in DAX.

When there's more than one distinct value of [Value] or it's not filtered, then we return your original measure.

[ConditionalDisplay] will fail if you have two rows for the same value of [Project] with multiple values of [Value].

greggyb
  • 3,728
  • 1
  • 11
  • 32
  • Thank you! Since I am a self-learner in Excel, where do I put these formulas? I believe I can't use them in the calculated field in PowerPivot since as far as I know it just allows for one expression (no variables). (I may be wrong) – Natalia Jan 06 '16 at 13:03
  • Not using any variables in these measure definitions. You add these as measures in the measure section of any Power Pivot table in the Power Pivot model. See the following links: https://msdn.microsoft.com/en-us/library/gg399077(v=sql.110).aspx https://msdn.microsoft.com/en-us/library/gg399161(v=sql.110).aspx – greggyb Jan 06 '16 at 14:51
  • Thanks! I did as described and the excel put these as two calculated fields by itself. However, I get the following error: "Calculation error in measure 'Table'[ConditionalDisplay]: The second and third arguments of function IF have different data types. This is not supported." – Natalia Jan 06 '16 at 15:38
  • What version of Excel and Power Pivot are you using? This is valid as tested in Power BI Desktop. – greggyb Jan 06 '16 at 18:47
  • Office 365, downloaded powerpivot for it 1 week ago – Natalia Jan 07 '16 at 16:10
  • Office 365 only offers office 2013 and 2016. Power Pivot is included with both of those versions of office without any need to download a separate add-in. I can confirm it *is not* functional on an Excel 2013 installation I have, and *is* functional on an Excel 2016 I have. – greggyb Jan 07 '16 at 18:58
  • ok, I seem to have 2013. Thank you for your solution, will see what I can do. – Natalia Jan 07 '16 at 20:06