Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
atziovara
Helper I
Helper I

Weighted Average with double filter Issue

  • I want to compute the Weighted Average for one of the following Questions, based on the value of the Answer to another Question. For example, I would like to compute the Weighted Average of the Liquidity Problems Intensity for companies who expect that their Demand in the next 6 months will increase (Question= "Expected change in demand in the next 6 months", Answer = 1). My dataset is like this:

 

Table Name: TableBI

WaveCompany IndexCompany IDWeightsQuestionsAnswersWeighted AnswersPeriods
111-10,006Demand change compared to the previous 6 months-1-0,0062022 H2
111-10,006Expected change in demand in the next 6 months10,0062023 H1
111-10,006Liquidity problems intensity10,0062022 H2
111-10,006Expected change in employment002023 H1
121-20,001Demand change compared to the previous 6 months10,0012022 H2
121-20,001Expected change in demand in the next 6 months10,0012023 H1
121-20,001Liquidity problems intensity-1-0,0012022 H2
121-20,001Expected change in employment10,0012023 H1
131-30,0057Demand change compared to the previous 6 months10,00572022 H2
131-30,0057Expected change in demand in the next 6 months002023 H1
131-30,0057Liquidity problems intensity002022 H2
131-30,0057Expected change in employment002023 H1
212-10,006Demand change compared to the previous 6 months10,0062023 H1
212-10,006Expected change in demand in the next 6 months002023 H2
212-10,006Liquidity problems intensity002023 H1
212-10,006Expected change in employment002023 H2
222-20,001Demand change compared to the previous 6 months10,0012023 H1
222-20,001Expected change in demand in the next 6 months10,0012023 H2
222-20,001Liquidity problems intensity-1-0,0012023 H1
222-20,001Expected change in employment10,0012023 H2
232-30,0057Demand change compared to the previous 6 months10,00572023 H1
232-30,0057Expected change in demand in the next 6 months002023 H2
232-30,0057Liquidity problems intensity-1-0,00572023 H1
232-30,0057Expected change in employment10,00572023 H2

*Weighted Answers is calculated as: Weights * Answers

**In the column "Answers" -1 denotes decrease (or zero problems for Liquidity problems intensity), 0 denotes stability (or low to medium intensity problems), and 1 denotes increase (or high intensity problems).

 

  • I created a disconnected table "Selected Characteristics" as:

 

SelectedCharacteristics =
SUMMARIZE( 'TableBI',
    'TableBI'[Questions],
   'TableBI'[Answers])

 

  • I created the following measure:

 

Weighted Average =
VAR SelectedQuestion1 = SELECTEDVALUE('TableBI'[Questions])
VAR SelectedQuestion2 = SELECTEDVALUE('SelectedCharacteristics'[Questions])
VAR SelectedPrice = SELECTEDVALUE('SelectedCharacteristics'[Answers])
RETURN
DIVIDE(
    SUMX(
        FILTER(
            'TableBI',
            NOT(ISBLANK('TableBI'[Weighted Answers])) &&
            NOT(ISBLANK('TableBI'[Weights])) &&
            'TableBI'[Questions] = SelectedQuestion1 &&
           CALCULATE(
               VALUES('TableBI'[Answers]), 'TableBI'[Questions] = SelectedCharacteristic2, 'TableBI'[Τιμή] = SelectedPrice, ALLEXCEPT('TableBI', 'TableBI'[Company Index])
           )
        ), 'Table'[Weighted Answers]
    ),
    SUMX(
        FILTER(
            'TableBI',
            NOT(ISBLANK('TableBI'[Weighted Answers])) &&
            NOT(ISBLANK('TableBI'[Weights])) &&
            'TableBI'[Questions] = SelectedCharacteristic1 &&
            CALCULATE(
               VALUES('TableBI'[Answers]), 'TableBI'[Questions] = SelectedCharacteristic2, 'TableBI'[Answers] = SelectedPrice, ALLEXCEPT('TableBI', 'TableBI'[Company Index])
           )
        ),
        'TableBI'[Weights]
    )
)

 

 

  • I added two slicers: one with Questions from TableBI and one with Questions and Answers from SelectedCharacteristics.
  • I added a Clustered Column Chart that has Waves on its X-Axis and Weighted Average on its Y-Axis
  • The problem that I am encountering is that this formula is only working when selected Answer = 1 or Answer = -1, however, when Answer = 0 I get back no values at all on the graph.
1 ACCEPTED SOLUTION
some_bih
Super User
Super User

Hi @atziovara Please check enclosed file two different tabs for two scenarios.

I created 6 different measures just for debug (it could be less number of measures).

Results are as in your example

Part 1 ratio = 0,50 and Part 2 ratio = -0,3276

Picture below for Part 2.

some_bih_0-1702753256916.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






View solution in original post

5 REPLIES 5
some_bih
Super User
Super User

Hi @atziovara Please check enclosed file two different tabs for two scenarios.

I created 6 different measures just for debug (it could be less number of measures).

Results are as in your example

Part 1 ratio = 0,50 and Part 2 ratio = -0,3276

Picture below for Part 2.

some_bih_0-1702753256916.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






some_bih
Super User
Super User

Hi @atziovara must ask: why your measure for wei.avg is not something like:

SUMX('Table', 'Table'[Weights]*'Table'[Answers])





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






@some_bih Thank you for your response!

The formula for the Weighted Averagethat I am using is a simple one!

 

It just sums the weighted answers (weights * answers) and divides the total by the sum of the weights.  I am using the typical mathematical formula: 
Weighted Mean = Σ (wi * xi) / Σwi


The filters that I am trying to use, which are neccesarry for drawing conclusions for business purposes, are creating the issue. I need to compute not only the Weighted Average for all the Questions posed to the companies, but I also for special cases, like "what is the weighted average of Liquidity problems intensity, for companies who expect that their demand will increase/stay stable in the next semester"?

 

My measure is working perfectly for all cases except when SelectedPrice = 0.

Hi @atziovara weight avg. are in your case standard, which is good.

So in short, you need weighted avg. for each question (as separate measure)?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






Hello @some_bih !

 

Thank you for your response!

 

Let me give you two examples of what I need to calculate: 

 

(1) Let's assume that I,the user, choose:

SelectedQuestion1 = "Liquidity problems intensity"

SelectedQuestion2 = "Expected change in demand in the next 6 months"

SelectedPrice = 1

 

In this case I would like to compute the Weighted Average of Liquidity problems intensity for the companies who have asnwered that the Expected change in demand in the next 6 months will be 1

 

The Questions & Answers in RED are the ones who check the conditions of the filtering for each Company ID. They ensure that the weighted average of Liquidity problems intensity for each company and wave will be calculated for SelectedQuestion2 = "Expected change in demand in the next 6 months" & SelectedPrice = 1. 

 

The weighted average is calculated as the sum of the Weighted Answers of Liquidity problems intensity (yellow background) divided by the sum of the Weights of Liquidity problems intensity (green background). It is based on the typical mathematical formula:

Weighted Mean = Σ (wi * xi) / Σwi

 

Therefore, the expected result is: [0,006 + (-0,001) + (-0,001)] / (0,006 + 0,001 + 0,001) = 0,5

 

atziovara_1-1702302602800.png

 

 

(2) SelectedQuestion1 = "Liquidity problems intensity"

SelectedQuestion2 = "Expected change in demand in the next 6 months"

SelectedPrice = 0

 

In this case I would like to compute the Weighted Average of Liquidity problems intensity for the companies who have asnwered that the Expected change in demand in the next 6 months will be 0

 

The logic is the same as above. 

The expected result is: [0 + 0 + (-0,0057)] / (0,0057 +0,006 + 0,0057) = -0,32759

 

atziovara_0-1702302587827.png

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors