CODEWITHSUNDEEP
powerbipowerbi-desktop
Alex Ivanov
Alex Ivanov

Reputation: 737

power bi: different calculations for several rows

Would you, please, help me to apply different calculations for 2 rows in power BI:

that is, to transform this table:

client_ids products purchased month
1          0        0         jan
2          1A       1         jan
2          1B       1         jan
3          0        0         jan
4          0        0         jan
5          0        0         feb

into this:

purchased jan feb
1         1   
0         3   1

That is, to perform calculations:

-on purchased = 0 - count over month, client

-on purchased = 1 - count distinct over month, client

Thank you.

I used the method:

-create the reference to the main query in the query editor

-drop the column with products

-drop duplicates

But this makes downloading the report slower.

Upvotes: 1

Views: 233

Answers (1)

Kin Siang
Kin Siang

Reputation: 2699

To return the expected output, you can use two steps to obtain the result from the data:

Assuming this is your table with date:

enter image description here

First, calculate the month different compared with today to find recently month (you can try other method depend on your data nature):

Mon Diff = (YEAR(NOW()) - YEAR(Sheet1[date])) + (MONTH(NOW()) - MONTH(Sheet1[date]))

Second, rank the recent month as current:

rank = 
var ranking = RANKX(Sheet1,Sheet1[Mon Diff],,,Dense)
return
SWITCH(ranking,1,"prior",2,"current")

Third, generate distinct values from purchase column

Table = DISTINCT(Sheet1[purchased])

Fourth, calculate the frequencies of 0 & 1 in Prior Month, the same for Feb

  Jan = CALCULATE(COUNT(Sheet1[rank]),Sheet1[rank]="prior",
        Sheet1[purchased]=EARLIER('Table'[purchased])) 


 Feb = CALCULATE(COUNT(Sheet1[rank]),Sheet1[rank]="current",
        Sheet1[purchased]=EARLIER('Table'[purchased]))

The New table for the infor (In Jan, purchase 2 has 2 occurrence instead of 1):

enter image description here

Upvotes: 1

Related Questions