DAX measure: how many stores have more than 65% of documents per article

[Anonymous]

Hi all,

I need some help to create DAX measures in PowerBI.  

I need to create a table (final table) where you can see per article

• How many distinct stores have the article
• how many documents there are for the article
• how many stores have more than 65% of documents per article
  • in the summary table you see the sub calculation for this
• which stores have more than 65% of documents per article

In the dashboard there will be a slicer so user can filter the data.

Below an example in Excel.

And here the sample data:

Store	Document	Article	Date
Zellik	1	zeep	2018
Aalst	2	zeep	2018
Brussel	3	zeep	2018
Parijs	4	zeep	2019
Aalst	5	zeep	2020
Brussel	6	zeep	2021
Parijs	7	zeep	2021
Brussel	8	zeep	2020
Parijs	9	zeep	2021
Gent	10	brood	2018
Brugge	11	brood	2018
Parijs	12	brood	2019
Brussel	13	brood	2019
Brussel	14	brood	2019
Brussel	15	brood	2019
Brussel	16	brood	2021
Ostende	17	brood	2021
Ostende	18	brood	2021
Brussel	19	brood	2021
Brussel	20	brood	2021
Brussel	21	brood	2021
Brussel	22	brood	2021
Brussel	23	brood	2021
Brussel	24	brood	2021

Hope someone can help!
Julie

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

All measures are in the attached pbix file.

Distinct Stores per article: =

COUNTROWS( VALUES( Data[Store] ))

Count Docs per article: =

COUNTROWS( VALUES( Data[Document] ))

Stores count >65%: =

VAR newtable =

SUMMARIZE ( Data, Data[Article], Data[Store] )

VAR add_columns =

FILTER (

ADDCOLUMNS (

newtable,

"@docscount", [Count Docs per article:],

"@alldocscount", CALCULATE ( [Count Docs per article:], ALLEXCEPT ( Data, Data[Article] ) )

),

[@docscount] / [@alldocscount] > 0.65

)

RETURN

COUNTROWS ( add_columns )

Stores name >65%: =

VAR newtable =

SUMMARIZE ( Data, Data[Article], Data[Store] )

VAR add_columns =

FILTER (

ADDCOLUMNS (

newtable,

"@docscount", [Count Docs per article:],

"@alldocscount", CALCULATE ( [Count Docs per article:], ALLEXCEPT ( Data, Data[Article] ) )

),

[@docscount] / [@alldocscount] > 0.65

)

RETURN

CONCATENATEX( add_columns, Data[Store], ", ")