Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
Below is my table with data
Product | Type | Sales |
pen | A | 7 |
Pen | A | 9 |
Pen | B | 7 |
Pen | B | 4 |
Pen | B | 10 |
Pen | B | 5 |
Pen | B | 8 |
Pen | C | 4 |
Pen | C | 4 |
Rubber | A | 2 |
Rubber | B | 2 |
Rubber | E | 4 |
Rubber | D | 4 |
Rubber | E | 10 |
Scale | A | 10 |
Scale | A | 4 |
Scale | A | 7 |
Scale | A | 3 |
Scale | B | 1 |
Scale | B | 7 |
Scale | C | 1 |
Scale | C | 1 |
Scale | D | 4 |
Scale | E | 8 |
Scale | E | 5 |
Output
Type | Obs | Ruled | Pinned |
A | 8 | 2 | 6 |
B | 6.8 | ||
C | |||
D | |||
E |
need to calcualte the avg for each product and type .. In Obs we calculate for pen , ruled for rubber and pinned for scale . i need this to be displayed in table not matrix table.
is there any possible to get the dax as calculated column rather than measure .
for ex:
in the obs for type A
there are 2 pens for type A ,so the avg is 8 .
This might help, write following DAX measures:
Amount=SUM([Values])
NumOfDistinctDates=DISTINCTCOUNT([Date])
Avg=DIVIDE([Amount],[NumOfDistinctDates])
@Anonymous
Hi,
Why do you need a calculated column if a measure is more suitable in this situation?
Also, do you already have a table with distinct Type values or you need to create one like the Output?
Create a table as follows
TableAv = SUMMARIZECOLUMNS(TableXX[Type], "Obs", CALCULATE(AVERAGE(TableXX[Sales]), TableXX[Product] = "Pen") ,
"Ruled" , CALCULATE(AVERAGE(TableXX[Sales]), TableXX[Product] = "Rubber"),
"Pinned", CALCULATE(AVERAGE(TableXX[Sales]), TableXX[Product] = "Scale"))
Thnks for ur reply ..
i have a filter with pen,rubber,scale .. so when i select the pen .. in the table only pen dax should excute or come . so when i select rubber & pen , in the table both rubber and pen data should come in column ..
can you help out with this ..
Hi @Anonymous ,
According to your description, what you want to achieve is to dynamically add or delete columns according to slicer.
After the table is created according to @HotChilli said before, you can refer to using-a-slicer-to-show-different-measures.
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |