Forum Discussion

luciefialova's avatar
luciefialova
Frequent Visitor
8 years ago
Solved

Getpivotdata equivalent

Hi,   Is there a Power BI equivalent to the getpivotdata function in Excel? Some teams within my organisation have quite messy reporting requirements (asking me to count apples and oranges) and in ...
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    Hi luciefialova,

     

    I would suggest you start to learn DAX language which could calculate all the expected result for you in Power BI without return back to SQL. And it's better to read a book from beginning. :smileyhappy:

     

    In addition, the formula(DAX) below to calculate the sum of the two totals mentioned above is for your reference.

    Measure =
SUMX (
    SUMMARIZE (
        'Attributes',
        'Attributes'[ATTRUBUTE_TYPE],
        "count of people", COUNTROWS ( Attributes )
    ),
    [count of people]
)
    + SUMX (
        SUMMARIZE (
            'transactionCF',
            'transactionCF'[CAMPAIGNCODE],
            "count of people", COUNTROWS ( transactionCF )
        ),
        [count of people]
    )

    Regards

luciefialova's avatar
luciefialova
Frequent Visitor
8 years ago

Hi,

 

Thanks for that. Maybe the problem is that I always have my SQL hat on and that's not how power BI works... I've made these two matrices, they are looking at count of people who had a tag added to their record and a count of people who made payments coded to a certain code. What the client wants to see is a sum of the two totals (130+18 and so on). In excel, I would have created these pivot tables on a hidden sheet and used getpivotdata to sum the two values and displayed just the sum figure. What I've done isn't very elegant (wait till you see my table structure :D I'm still working this out!!)

 

 

The attributes table is behind the first matrix and the transactionCF.... table behind the second one (that query finds people who made transaction AND don't have any of those attributes).

 

What's in the two tables:

 

Thanks

Lucie

luciefialova's avatar
luciefialova
Frequent Visitor
8 years ago

They'll then want to see even more complicated indicators such as the count of people who had attribute A added in a time period oct/16-now against the count of people who had attribute B added in this financial year who had attribute A added in that previous time period. I used to get the numbers for them just running SQL queries every quarter but got bored of that and now I'm trying to create something in Power BI that I don't have to touch again.