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,
I have a Data that looks similar to this:
Type | Amount | Number | Average Price |
A | 1.000 | 10 | 100 |
B | 2.000 | 10 | 200 |
C | 3.000 | 40 | 75 |
Total | 6.000 | 48 | 125 |
Amount is just a SUM.
Average Price y just an Average.
Number is
DIVIDE(AMOUNT;AVERAGE PRICE;BLANK())
Like I wanted the total row Number represent the sum of the rows A, B and C and not the Division of Amount and Average Price, I did this:
Number = IF( HASONEFILTER([Type]) ; DIVIDE([Amount] ; [Average Price] ; BLANK()) ; SUMX ( SUMMARIZE ( VALUES ( [Type] ); [Type]; "AB"; DIVIDE ( [Amount]; [AVGPrice]; BLANK () ) ); [AB] )
And it works for that table.
But the problem is when I want to represent the same data and measures but with a different dimension.
Imagin that now I want to see it instead of by type, by Country, like this:
Country |
Z |
X |
Y |
Total |
The measure won't work because just works in case that type is filtered ...
Any ideas to solve the problem that will work with all the dimensions of my model?
Thank you in advance!!
Give this a shot
Number = IF ( HASONEFILTER ( [Type] ), DIVIDE ( [Amount], [Average Price], BLANK () ), SUMX ( ALLSELECTED ( TABLENAME ), DIVIDE ( [Amount], [Average Price], BLANK () ) ) )
@Zubair_Muhammad thanks for your answer but it doesn't work even with the [Type] table visual.
And I think that your formula would work only with a table visual with the dimension [Type] and not with others.
Anyone wit more ideas? It's really urgent for me.
If I don't find a solution I'll have to do a lot of different meassures, one for each dimension. And it's not efficient at all...
HI @luxpbi,
Maybe you can try to use row count as calculate condition :
Number = IF ( COUNTROWS(Table)=1, DIVIDE ( [Amount], [Average Price], BLANK () ), SUMX ( ALLSELECTED ( Table ), DIVIDE ( [Amount], [Average Price], BLANK () ) ) )
Regards,
Xiaoxin Sheng
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 | |
98 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |