Measures to rows / columns?

[Misca]

I'm a PowerPivot newbie and once again I'm stuck in the very basics of using the PowerBI.


Here's my table setup:


Table fSales has fields like StoreID, ProductID, Payment Method and Sales Amount. It's joined to TbStores with the StoreID, tbProducts with the ProductID and tbPAymentMethods with the PaymentMethod field.
TbStores has fields like StoreID and CompanyID. It's joined to the tbCompanies with the CompanyID field.
The rest of the tables have just general data about the companies, products and payment methods.

Each company has 1 or more stores and each store uses 1 or more different payment methods for each product they're selling.


I used the DISTINCTCOUNT to get a measure of different payment methods in the fSales table and the measure works fine to show how many different methods of payment individual companies are using.


Now what I'd like to do next is create a histogram showing how many companies are using how many different payment methods, ie. I'd like to get the distinct count numbers to my rows or columns area of my PT but I can't use measure in those fields. How could I turn those calculations into row fields? I believe this might help solving my other problems as well.


Another problem I just can't solve is how to get the count of companies per product and per payment method. I mean the distinct count tells me the number of stores or companies but I'd need the table to count the same company once in each field it appears. I just can't figure out a way to get from the StoreID level to the Company level.

Ultimately I'd like to be able to filter the data and see how many companies using payment method A are also using payment methods B and C but I'm not even sure if that's possible with Power Pivot.


Thanks for your help!

 

[Gordonik]

You can not use a measure in rows or coumns. But you can put in a row a CompanyID and if your measure is properly calculated should give you a distinct number of payments per comapny. You are describing your measue but did not share the syntax. The same for data model. It is easier to look at the screenshot.

The below measure can be used

Your distinnctcount measure might look like this  =CALCULATE(DISTINCTCOUNT(fSales[Payment  Method]);fSales;TbStores;tbProducts;tbPAymentMethods;tbCompanies)

As far your second issue. Use the above measure in your pivot table. Drag "company", "product" and "payment method" to a row field of pivot table

 

[Misca]

Thanks for your help. Unfortunately it returns exactly the same values as my original DISTINCTCOUNT(fSales[Payment Method]).

Trying to insert an image of my table setup. Hope it works.

Trying to get the formula to show the number of companies using each payment method and their NetSales for each payment method. Since most of the companies are using more than one payment method the total number of companies per payment method should be more than the number of companies.

 

[Gordonik]

In a data model this measure gives the same value but when you place it in a pivot table you should get the expected numbers. Have you tried this?
Try this measure in your pivot table. Add the CompanyID from the TbStores or the TbCompnies to the row area and share the pivot table screenshoot.
Are you sure total is a sum of its partias? Check it

 

[Misca]

It gives the same value in pivot tables ie. 1 for each combination of company & matching payment method but I still can't sum them up: The total of each payment methods is always the number of payment methods, not the number of companies using the payment method. I've read how Power Pivot works and calculates the numbers but it's still weird to see how the total of a tall column full of ones is still one, not the sum or count of those numbers.

But I believe I've figured out the solution: I should get the numbers I want if I take the distinct count of the combination of company & payment method. Never thought I'd end up using helper columns in Pover Pivot but it should do the trick.