Power BI pivots and calcs

[AndreT]

Hi guys,

I just got stuck on a query I'm trying to build in PBI.

In excel what I usually would do is create a Pivot table which would give me the first result and from that result I would than create a function. I.e Pivot would indicate (count) no of traders per PCA (Post Code Area). then, I would create a function COUNTIFS(criteriarange1,criteria1,criteriarange2,criteria2) and I would get my total count of PCA with no of traders greater than 3.

What would be a DAX query for that?

[v-huizhn-msft]

Hi @AndreT,

Please create some sample table and listed expected result. It's hard to reproduce your scenario without date. You can create fake data if your data is confidential.

Best Regards,
Angelia

[AndreT]

Hi sorry for a late reply.

Basically I’d like to create 2 matrixes

First (It has been done) shows number of traders by trade by PcD.

I.e :

PcD   Plumber Drains Electrician

AB1       1              2              3

Ab2       2              2              2

B1         0              4              3             

B2         3              0              3             

BL3       4              2              1             

Data behind it is coming from our DB. And contains all traders, trades and PcD covered by each of trader.

2nd and that’s where I have a problem showing number of PcD covered in each trade

I.e.

                                       Plumber   Drains    Electrician

No Of PcD Covered         2500          2100    1900

So the 2nd matrix is kind of a result of the first one. Is there a DAX formula which would help with this visualisation?

[v-huizhn-msft]

Hi @AndreT,

You must share the how get the second matrix from first one, what are the rules?

Best Regards,
Angelia

[AndreT]

Hey @v-huizhn-msft & @Ashish_Mathur,

Ok so I have created a dummy dataset ( Dummy sample ) in Excel.

From which Matrix 1 is created (it’s a pivot but looks the same in PBI).

Then from that, 2nd matrix is created.

And that’s where I have a problem. How to get to this 2nd Matrix in PBI.

Hope that helps.  It's like a pivot of a Pivot, in an excle way of thinking.

Best Regards

Andre

[AndreT]

@v-huizhn-msft@Ashish_Mathur

Does that help?

[Ashish_Mathur · Accepted solution]

Hi @AndreT,

You may refer to my solution here.  Hope this helps.

[AndreT]

Hi @Ashish_Mathur

I applied this piece of DAX code to the actual model and it worked perfectly. Thank you!

[Ashish_Mathur]

You are welcome.

[Ashish_Mathur]

Hi,

Share the link from where i can download your file.  Also, with respect to the data on that file, show the expected result.

[AndreT]

Hi Ashish,

unfortunately I can't share this file. Is there any way you could explain the process in a post?

As per the example provided?

[Ashish_Mathur]

Hi,

Create a dummy dataset