Matrix Visual : i need to get the average from shown numbers in the visual. Is it even possible?

[volt26]

Hello!

I tried to to an answer to my issue in another post but I poorly described my issue. I would like to explain it better here.

Someone sent me a very complicated pbix to play with. Its creator is no more in the company. I'm not a data analyst so there are a lot of things I have trouble to understand.

My colleague wants me to get a table with the avg sales per user (row) per cycle (column). Fortunately, the creator of the file created all the formulas I needed to get this :

This is a Matrix table with this in it : 

NOM_PRENOM is a column a of table, it is the names from a table with all employees in it
CYCLE_SEQ is a column a of table, is a predetermined cycle (like a season, a month, etc)
M_Billet_jour is a Measure. I can't give it here but it looks like this : 

divide([Measure_A],[Measure_B],0)

Measure_A contains this :
CALCULATE(COUNT(MAIN_TABLE[ID_COLUMN]),ALL('MAINT_TABLE'[SOURCE_COLUMN]))

Measure_B contains this : 
CALCULATE(DISTINCTCOUNTNOBLANK(MAIN_TABLE[DATE_COLUMN]),ALL('SECONDARY_TABLE'[CATEGORY_COLUMN]))

Is there a way to display the average of the data shows in my matrix table?
Like.. 1st line is (1,1+0,6+0,6+1,4)/amount of non empty columns in THIS row = 3.7/4 = 0,925
2nd line is (1,2+0,2)/amount of non empty columns in THIS row = 1,4/2 = 0,7

From all the read I did, and all the videos I watched, it seems possible if the data shown in the matrix visual is in a table somewhere but it isn't and it cannot be because these numbers you see are from the formulas I showed you and there are filters on them (selected cycles, selected teams, etc..)

I just can't find a way around this. It seems to be a very complicated problem. I can quite easily export the data in a csv and then manually import it in excel to create the excel average column I need but I would prefer to have it all in Power BI.

I would love to share the PBIX file here but the data it contains can't be shared. And all these tables are very complicated, I just can't find a way to downstream it in a sample file to share with you. I'm about to give up but I figured if there is one person that can help, this person might just be here!

Thank you!

[Anonymous]

Hi @volt26 ,

According to your description, here are my steps you can follow as a solution.

(1) We can create a measure. 

Measure = 
VAR _a=SUMX(FILTER(ALLSELECTED('Table'),'Table'[NOM_PRENOM]=MAX('Table'[NOM_PRENOM])),[M_Billet_jour])
VAR _b=COUNTROWS(FILTER(ALLSELECTED('Table'),'Table'[NOM_PRENOM]=MAX('Table'[NOM_PRENOM]) && [M_Billet_jour] <>BLANK()))
RETURN DIVIDE(_a,_b)

(2) Then the result is as follows.

Best Regards,

Neeko Tang

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

[volt26 · Accepted solution]

Unfortunately, I didn't understand your answer well enough to make it work.
All I get as a result is the same data :

In the meantime, I found a workaround that I would like to share here. I'm aware this is not what I asked for at first but the more time I spend on this problem, the more I understood the matrix visual wasn't required.

I did a table with custom measures that filter the cycles (each measure has its own cycle)
It's not as effective because this doesn't let the user filter the cycles by himself but it wasn't requested by the user.

The formula looks like this :

M_Billet_Jour_Cycle1 = CALCULATE([M_Billet_jour],D_DATE[CYCLE]="1")

The user can still filter the year and the table is premade with columns for each cycle
 

The formula for the average can certainly be optimized but my barebone knowledge made me do this :

M_Billet_jour_Moyenne_Globale =
var M_Billet_jour_Cycle1 = IF(ISBLANK(CALCULATE([M_Billet_jour],D_DATE[CYCLE]="1")),0,1)
var M_Billet_jour_Cycle2 = IF(ISBLANK(CALCULATE([M_Billet_jour],D_DATE[CYCLE]="2")),0,1)
var M_Billet_jour_Cycle3 = IF(ISBLANK(CALCULATE([M_Billet_jour],D_DATE[CYCLE]="3")),0,1)
var M_Billet_jour_Cycle4 = IF(ISBLANK(CALCULATE([M_Billet_jour],D_DATE[CYCLE]="4")),0,1)
var M_Billet_jour_Cycle5 = IF(ISBLANK(CALCULATE([M_Billet_jour],D_DATE[CYCLE]="5")),0,1)
var M_Billet_jour_Cycle6 = IF(ISBLANK(CALCULATE([M_Billet_jour],D_DATE[CYCLE]="6")),0,1)
var M_Billet_jour_Cycle7 = IF(ISBLANK(CALCULATE([M_Billet_jour],D_DATE[CYCLE]="7")),0,1)
var M_Billet_jour_Cycle8 = IF(ISBLANK(CALCULATE([M_Billet_jour],D_DATE[CYCLE]="8")),0,1)
var M_Billet_jour_Cycle9 = IF(ISBLANK(CALCULATE([M_Billet_jour],D_DATE[CYCLE]="9")),0,1)
var M_Billet_jour_Cycle10 = IF(ISBLANK(CALCULATE([M_Billet_jour],D_DATE[CYCLE]="10")),0,1)
var M_Billet_jour_Cycle11 = IF(ISBLANK(CALCULATE([M_Billet_jour],D_DATE[CYCLE]="11")),0,1)
var Diviseur = M_Billet_jour_Cycle1 + M_Billet_jour_Cycle2 + M_Billet_jour_Cycle3 + M_Billet_jour_Cycle4 + M_Billet_jour_Cycle5 + M_Billet_jour_Cycle6 + M_Billet_jour_Cycle7 + M_Billet_jour_Cycle8 + [M_Billet_Jour_Cycle9] + [M_Billet_Jour_Cycle10] + [M_Billet_Jour_Cycle11]
var Total = ([M_Billet_Jour_Cycle1] + [M_Billet_jour_Cycle2] + [M_Billet_jour_Cycle3] + [M_Billet_jour_Cycle4] + [M_Billet_jour_Cycle5] + [M_Billet_Jour_Cycle6] + [M_Billet_Jour_Cycle7] + [M_Billet_Jour_Cycle8] + [M_Billet_Jour_Cycle9] + [M_Billet_Jour_Cycle10] + [M_Billet_Jour_Cycle11])/Diviseur

RETURN Total

The IFs are to find if I should increase the divider by 1 or not (yes if the value isn't empty). Then I sum all the dividents and divide by the divider.

Thank you very much for your help. Once I get better at Power BI, I might be able to do this with a matrix table! 🙂