Forum Discussion

daniel_krantz_4's avatar
daniel_krantz_4
Helper I
2 years ago
Solved

Grouping data in the table view

I am new to Power Bi and am having issues figuring this out. I want to have a column that gives average sales by provider by month. Each row is a day, I have a "month year" column", and then there ar...
Need Help
  • _AAndrade's avatar
    _AAndrade
    2 years ago

    I'm using this table to simulate tour data set:


    I added a new column wich computes what I understoud from your explanation. The measure is this:

     

    Column = 
VAR _CurMonth = T_Sales[Month]
VAR _Provider = T_Sales[Provider]
VAR _Result = 
    CALCULATE(
        AVERAGE(T_Sales[Sales]),
        FILTER(
            ALL(T_Sales),
            T_Sales[Month] = _CurMonth && T_Sales[Provider] = _Provider
        )
    )
            
RETURN
    _Result

     


    And the final output is this:

    For example, for provider A in January the Average is 150 ((100 + 200 / 2)), so this number repeats for each day of January for that provider. In February the Average will be diferent.
    This is what you're looking for?

_AAndrade's avatar
_AAndrade
Resident Rockstar
2 years ago

Could you provide a sample of your data and the expected output so I can take a look?

 

daniel_krantz_4's avatar
daniel_krantz_4
Helper I
2 years ago

So the idea is that for each row in July for each provider that extra empty column would show the average sales in July on each row (same number would repeat a couple times, then a new provider so a new number would repeat a couple times, then a new month so it all iterates again.