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 like below and i want to calculate the average by distinct divident category.
Data:
Month | Country | Store | THRS | Value | %pctn |
APR | Singapore | SG1 | 200 | 50 | 25.00 |
APR | Singapore | SG1 | 200 | 60 | 30.00 |
APR | Singapore | SG2 | 100 | 20 | 20.00 |
now i want to calculate the average by Store:
Expected result:
Avg | |
SG1 | 27.50 |
SG2 | 20.00 |
The dax i tried:
Solved! Go to Solution.
Hi @SEPA ,
Using your measures will give you the results you expect.
Or am I not understanding your needs? If you want to use different measure for different stores, you can try using the SWITCH functions. For example.
Measure =
SWITCH(
TRUE(),
MAX('Fact'[Store]) = "SG1",[measure1_mecaverage_by_str_monthly_trend],
MAX('Fact'[Store]) = "SG2",[measure2_mecaverage_by_str_monthly_trend])
Then drag the measure and field [Store] into the table visual.
The pbix file is attached for reference.
Best Regards,
Community Support Team_Gao
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems with it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
Dear @SEPA,
Please try using this measure.
Average of country =
CALCULATE (
AVERAGEX (
VALUES ( 'Fact'[Store] ),
[measure1_mecaverage_by_str_monthly_trend]
),
ALL ( 'Fact' )
)
The pbix file is attached for reference.
Best Regards,
Community Support Team_Gao
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems with it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
Hi @SEPA ,
Using your measures will give you the results you expect.
Or am I not understanding your needs? If you want to use different measure for different stores, you can try using the SWITCH functions. For example.
Measure =
SWITCH(
TRUE(),
MAX('Fact'[Store]) = "SG1",[measure1_mecaverage_by_str_monthly_trend],
MAX('Fact'[Store]) = "SG2",[measure2_mecaverage_by_str_monthly_trend])
Then drag the measure and field [Store] into the table visual.
The pbix file is attached for reference.
Best Regards,
Community Support Team_Gao
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems with it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
Dear V-Cgao-msft,
Thanks for your response on this. And, yes the distinct average for store is comign fine, but however i am facing issue while calculating average for the calculated measure.
below is expected,
month | country | store | thrs | values | Avergae of each record | Average of Store | Average of country |
APR | Singapore | SG1 | 200 | 50 | 25.0% | 27.50% | 23.75% |
APR | Singapore | SG1 | 200 | 60 | 30.0% | ||
APR | Singapore | SG2 | 100 | 20 | 20.0% | 20.00% | |
this one is average of G |
need to find out average of country from already calculated measure (average of store)
yes i was looking the above output
Dear @SEPA,
Please try using this measure.
Average of country =
CALCULATE (
AVERAGEX (
VALUES ( 'Fact'[Store] ),
[measure1_mecaverage_by_str_monthly_trend]
),
ALL ( 'Fact' )
)
The pbix file is attached for reference.
Best Regards,
Community Support Team_Gao
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems with it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
Thank you, it works for me...
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 |
---|---|
49 | |
26 | |
21 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |