I need to count and add certain parameters of a table under certain criteria

[Syndicate_Admin]

Hello, maybe it is something easy, but I do not find the formulas to make me the count and sum that I look for on a table under certain criteria, to see if you can help me with the following:

I have a table of book stores, in this example I have put very few provinces to summarize data, but they would be stores distributed in all the provinces of Spain:

I want to get to the result that I put a little below where, according to the typology described in the last Column and by Provinces, tell me numbers of Stores and add number of books, so that the "Paper + Digital Typology" would be a total by province of everything there is (both Paper + Digital + Only Digital) and on the other hand it would be Only Digital that would be a count of centers and a sum of books of only those stores that do not have paper books.

Here's the result in excel of what I want to get a Power Bi table:

As I said before, it is really about many provinces and I do not think the solution is to filter specifically by the name of each of them, surely there is a way to do it but I am a beginner and I do not find the formulas.

Greetings and thanks in advance

[Anonymous · Accepted solution]

Hi  @Syndicate_Admin ,

Here are the steps you can follow：

1. In Power query. Add Column – Index Column – From 1.

2. Create measure.

Ndetiendas =
var _indexTIP=CALCULATE(MAX('Table'[Tipologia]),FILTER(ALL('Table'),'Table'[Index]=MAX('Table'[Index])))
var _indexplus1=CALCULATE(MAX('Table'[Tipologia]),FILTER(ALL('Table'),'Table'[Index]=MAX('Table'[Index])+1))
var _indexnegative1=CALCULATE(MAX('Table'[Tipologia]),FILTER(ALL('Table'),'Table'[Index]=MAX('Table'[Index])-1))
return
 SWITCH(
     TRUE(),
 _indexTIP <> _indexplus1 && _indexTIP <> _indexnegative1 ,MAX('Table'[Toal Libros]),
_indexTIP =_indexplus1&&MAX('Table'[Index])<>MINX(ALL('Table'),'Table'[Index]) ,CALCULATE(SUM('Table'[Toal Libros]),FILTER(ALL('Table'),'Table'[Provincia]=MAX('Table'[Provincia]))),
_indexTIP =_indexplus1&&MAX('Table'[Index])=MINX(ALL('Table'),'Table'[Index]) ,MAX('Table'[Toal Libros]),
_indexTIP =_indexnegative1,CALCULATE(SUM('Table'[Toal Libros]),FILTER(ALL('Table'),'Table'[Provincia]=MAX('Table'[Provincia]))))

Ndelibros =
var _indexTIP=CALCULATE(MAX('Table'[Tipologia]),FILTER(ALL('Table'),'Table'[Index]=MAX('Table'[Index])))
var _indexplus1=CALCULATE(MAX('Table'[Tipologia]),FILTER(ALL('Table'),'Table'[Index]=MAX('Table'[Index])+1))
var _indexnegative1=CALCULATE(MAX('Table'[Tipologia]),FILTER(ALL('Table'),'Table'[Index]=MAX('Table'[Index])-1))
return
 SWITCH(
     TRUE(),
 _indexTIP <> _indexplus1 && _indexTIP <> _indexnegative1 ,COUNTX(FILTER(ALL('Table'),'Table'[Tipologia]=MAX('Table'[Tipologia])&&'Table'[Provincia]=MAX('Table'[Provincia])),[Provincia]),
_indexTIP =_indexplus1&&MAX('Table'[Index])<>MINX(ALL('Table'),'Table'[Index]) ,COUNTX(FILTER(ALL('Table'),'Table'[Provincia]=MAX('Table'[Provincia])),[Provincia]),
_indexTIP =_indexplus1&&MAX('Table'[Index])=MINX(ALL('Table'),'Table'[Index]) ,COUNTX(FILTER(ALL('Table'),'Table'[Tipologia]=MAX('Table'[Tipologia])&&'Table'[Provincia]=MAX('Table'[Provincia])),[Provincia]),
_indexTIP =_indexnegative1,COUNTX(FILTER(ALL('Table'),'Table'[Provincia]=MAX('Table'[Provincia])),[Provincia])
)

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[ManguilibeKAO]

Hi,

Assuming  that your input data is in a Power BI table named Book Stores, you can use the following method:

a) Create a measure Mesmm  like this:

Mesmm =

Var v= MAXX('Book Stores',[Tipologia])

return

IF

(

v="PAPEL + DIGITAL",

Calculate(

CountRows('Book Stores'),ALL('Book Stores'[Tipologia])

),CountRows('Book Stores')

)

b) Create another measure Mesmm2 like this:

Mesmm2 =

Var v= MAXX('Book Stores',[Tipologia])

return

IF

(

v="PAPEL + DIGITAL",

Calculate(

SUM('Book Stores'[No Libros Papel]) +

SUM('Book Stores'[N° Libros Digital]),ALL('Book Stores'[Tipologia])

),

SUM('Book Stores'[No Libros Papel]) +

SUM('Book Stores'[N° Libros Digital])

)

c) Now, you drag a table onto the Report View, and after that,  you drag the columns Typologia and  Provincia in this table Visual. After that you darg the measures Mesmm and Mesmm2 in the table Visual and you'll have the result you are looking for.

In case it answered your question, please accept the solution to help other members of this community to find it.

Best Regards.

Manguilibe KAO