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?

 

 

v-yingjl's avatar
v-yingjl
Community Support
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.