Multiply percent of column total with a value in each group

[RevD]

Need Help 

i want to mutiply the Percent of column total in each category with Weight value in each category by Quarters 

Current Quat Count =COUNT(Sheet1[Index]) 

Previous = IF(HASONEVALUE(Sheet1[DateCustom]),CALCULATE([Counts],VALUES(Sheet1[Category ]),DATEADD(Sheet1[DateCustom],-1,QUARTER)),blank())

overallCounts = ([Counts]+[Previous]) / (2 - ISBLANK([Counts]) - ISBLANK([Previous]))

Percent of column total of : Overalltotal is displayed below 

2019 - 01 = 20%*60% + 25%*40% 

2019 - 04 = 31% *30% + 38%*50% 

and display the final output like this format : 

Below is the data set

Index	Category	DateCustom	Rating	WEIGHT
1	Food	1/1/2019	Good	0.4
2	Food	1/1/2019	Good	0.4
31	Food	1/1/2019	Bad	0.4
3	Food	1/1/2019	Verygood	0.4
4	Food	4/1/2019	Verygood	0.5
5	Food	4/1/2019	Verygood	0.5
6	Food	4/1/2019	Bad	0.5
7	Food	4/1/2019	Bad	0.5
8	Food	7/1/2019	Verygood	0.3
9	Food	7/1/2019	verygood	0.3
10	Food	7/1/2019	Bad	0.3
11	Food	7/1/2019	Bad	0.3
12	Food	7/1/2019	Bad	0.3
13	Cloths	1/1/2019	Good	0.6
14	Cloths	1/1/2019	Good	0.6
15	Cloths	1/1/2019	Bad	0.6
16	Cloths	1/1/2019	Verygood	0.6
17	Cloths	1/1/2019	Verygood	0.6
18	Cloths	4/1/2019	Good	0.3
19	Cloths	4/1/2019	Good	0.3
20	Cloths	4/1/2019	Good	0.3
21	Cloths	4/1/2019	Verygood	0.3
22	Cloths	4/1/2019	Verygood	0.3
23	Cloths	4/1/2019	Bad	0.3
24	Cloths	4/1/2019	Bad	0.3
25	Cloths	4/1/2019	Bad	0.3
26	Cloths	7/1/2019	Verygood	0.2
27	Cloths	7/1/2019	Verygood	0.2
28	Cloths	7/1/2019	Bad	0.2
29	Cloths	7/1/2019	Bad	0.2
30	Cloths	7/1/2019	Bad	0.2

[v-zhenbw-msft · Accepted solution]

Hi @RevD ,

We can create a measure to meet your requirement.

1. First we need to create a calculate column to judge Top or Bottom.

Rating(Groups) = IF([Rating] in {"Good","Verygood"},"Top","Bottom")

2. Then we can create a measure to get the result.

Current Quat Count = 
var Cloth_weight = CALCULATE(MIN(Sheet1[WEIGHT]),FILTER(Sheet1,Sheet1[Category] = "Cloths"))
var Food_weight = CALCULATE(MIN(Sheet1[WEIGHT]),FILTER(Sheet1,Sheet1[Category] = "Food"))
var Count_Cloth = CALCULATE(COUNT(Sheet1[Index]),FILTER(Sheet1,Sheet1[Category]="Cloths"))
var Count_food = CALCULATE(COUNT(Sheet1[Index]),FILTER(Sheet1,Sheet1[Category]="Food"))
var Count_Allselected_cloth = CALCULATE(COUNT(Sheet1[Index]),FILTER(ALLEXCEPT(Sheet1,Sheet1[DateCustom]),Sheet1[Category]="Cloths"))
var Count_Allselected_food = CALCULATE(COUNT(Sheet1[Index]),FILTER(ALLEXCEPT(Sheet1,Sheet1[DateCustom]),Sheet1[Category]="Food"))
var Cloth_percentage = Count_Cloth / Count_Allselected_cloth
var food_percentage = Count_food / Count_Allselected_food
return
food_percentage*Food_weight + Cloth_percentage*Cloth_weight

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

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

[RevD]

thank you the solution is working 

One more help on this : But i am not able to get this, Can you provide Dax. below is the DAX i use

_Count = CALCULATE(COUNTX(Sheet1,Sheet1[Index]),FILTER(Sheet1,NOT(ISBLANK(Sheet1[Index]))))

_PreviousCounts = IF(HASONEVALUE(Sheet1[DateCustom]),

CALCULATE([_Count],VALUES(Sheet1[Category]),DATEADD(Sheet1[DateCustom],-1,QUARTER))

,BLANK())

_AccuracyRollingAvg = ([_Count ]+[_PreviousCounts ] )/

(2-ISBLANK([_Count ])-ISBLANK([_PreviousCounts ]))

Currently i use this _AccuracyRollingAvg to display by Quarters for each Category and it works good. In the new table i want to display sum of _AccuracyRollingAvg by Category  like below. Is it possible to use _AccuracyRollingAvg in Sumx

[RevD]

The user wanted to display Quarters in Q1 2019 and so on. so created a date table and did calculation. I wanted to calculate the percent of column total which is not working. 

Below is the code i am using in Steps :

counts = CALCULATE(COUNTX(Sheet1,Sheet1[Index]),FILTER(Sheet1,NOT(ISBLANK(Sheet1[Index]))))

PreviousCounts = IF(HASONEVALUE(Sheet1[DateCustom]),

CALCULATE([counts],VALUES(Sheet1[Category]),PREVIOUSQUARTER('Date'[FullDate]))

,BLANK())

RollingAvg = ([counts]+[PreviousCounts] )/

(2-ISBLANK([counts])-ISBLANK([PreviousCounts]))

1. i want to get the Total of each column display in row, This is not working 

ClothsOverallcounts = CALCULATE(

    [RollingAvg],

    Sheet1[Category] IN { "cloths" },ALLEXCEPT('dATE','dATE'[Format_QuarterShort_YYYY]))

In this example for q1-2019 Cloths i want to display 5 in all rows for ClothsOverallcounts. Basically i want to get the % of column total and multiply with Weighted percent 60%

2. How to achive Sum of RollingAvg quaters by Category. Currently it just gives the of count by categorys