Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Power BI Matrix Row Calculations

Here is my source data:     Here is the matrix I am trying to create in Power BI:     I am running into a few issues such as performing the Count/Sale calculation and assigning diff...
Need Help
  • v-yingjl's avatar
    v-yingjl
    6 years ago

    Hi Anonymous ,

    Create this measure to show the result:

    avg = 
VAR _sale =
    CALCULATE (
        SUM ( test[Value] ),
        ALLEXCEPT ( 'test', test[Year], test[Region] )
    )
VAR _count =
    CALCULATE (
        SUM ( 'test'[Value] ),
        ALLEXCEPT ( test, test[Year], test[Region] ),
        'test'[Measure] = "Count"
    )
RETURN
    IF (
        ISINSCOPE ( 'test'[Measure] ),
        IF (
            SELECTEDVALUE ( 'test'[Measure] ) = "Count"
                || SELECTEDVALUE ( 'test'[Measure] ) = "Sale",
            SUM ( 'test'[Value] ),
            _sale / _count
        ),
        SUM ( test[Value] ) + _sale / _count
    )

     

    Best Regards,
    Yingjie Li

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

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

I tried using this solution.  One issue in my matrix is I need to show Sales and Count as a SUM, but the Result as an Average.  How could I do this?

 

 

lbendlin's avatar
lbendlin
Super User
6 years ago

Don't use these aggregation functions. Instead create separate measures as I suggested.  Then you can control the behavior both inside the visual and in the totals (via HASONEVALUE() etc)